1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel macro help req

Discussion in 'Business Applications' started by snoozee, Apr 10, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    259
    i have created a spreadsheet that has a list of names down the left, days of the week and dates across the top and a "user' choose in the data fields.The spreadsheets are in one workbook and i have a sheet for each month.

    What i need to do is create a form that will enable users to choose a "Date" period ie start and end dates and choose a "name". i then need this form to have a button on it that when clicked would generate an email to the relative person named which would have all of the relevant data in it.

    I know how to create the actual form, it's linking everything to it thats the problem, so would really appreciate some help.

    Hope this all makes sense !!!
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    Hi snoozee, yes it makes sense but it is difficult to advise you without knowing the cell locations of the data and name of the form etc. Can you post a zipped copy of the workbook on here?
    By the way, do you have Access, as this is really an Access application rather than Excel?
     
  3. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    259
    i do have access and your right it would be easier, yet this a work project and users only have access to excel. i'll attach the attachment later as i need to mail it home from work and thanks for your response and offer of help it's greatly appreciated
     
  4. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    259
    I have attached my file. The formating for April 06 is what the spreadsheet will look like eventually. i need for the user to be able to send a mail that contains day and time information.

    Thanks for your help
     

    Attached Files:

  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    As a possible start point, check out the "Sending Personalized Email from Excel" code at John W's site (http://www.j-walk.com/ss/excel/tips/tip86.htm) ; you might be able to adapt it to fit your needs. For example, with a "Compose message" section as follows:

    Msg = ""
    Msg = Msg & "Dear " & Cells(ActiveCell.row(), 1) & "," & vbCrLf & vbCrLf
    Msg = Msg & "Here's your schedule:" & vbCrLf & vbCrLf

    For Each Cell In Selection
    Msg = Msg & Cells(2, Cell.Column) & " " & Cells(3, Cell.Column) & vbCrLf
    Msg = Msg & Cell & vbCrLf
    Msg = Msg & "Start time: " & Format(Cells(Cell.row + 1, Cell.Column), "hh:mm") & vbCrLf
    Msg = Msg & "End time: " & Format(Cells(Cell.row + 2, Cell.Column), "hh:mm") & vbCrLf & vbCrLf
    Next Cell


    , you'd get the following when run with April_06!J22:L22 selected:

    Dear Janice,

    Here's your schedule:

    Tuesday 11/04/2005
    Nae Nae
    Start time: 09:00
    End time: 15:00

    Wednesday 12/04/2005
    Nae Nae
    Start time: 09:00
    End time: 15:00

    Thursday 13/04/2005
    Nae Nae
    Start time: 09:00
    End time: 15:00


    However, this does highlight the fact that your dates in row 3 are not correct.

    Rgds,
    Andy
     
  6. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    259
    Thanks for this, at least this gives me starting point
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    The add-ins at Ron de Bruin's site are another option, if add-ins are allowed in your environment.

    http://www.rondebruin.nl/
     
  8. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    259
    the j-walk link is invaild can you resubmitt and thanks for your help
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    Removed the ")", works now.
     
  10. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    259
    Thanks for that. I have been having a play today, yet i've done something wrong as it keeps generating heaps of messages (I had to shut my PC down the first time it happpened as i couldn't stop them from generating !!)

    I've tried altering the code yet it still isn't a happening thing (I'm still a learner when it comes to this !!).

    I've listed the code below and would appreciate some help in advising what i'm missing or have that i shouldn't !!


    i also can't seem to get it to pick up the email address either (it's sitting in cell ref A40)

    Private Declare Function ShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
    ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) As Long

    Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String

    Dim r As Integer, x As Double
    For r = 3 To 40 'data in rows 3-40

    ' Get the email address
    Email = Cells(r, 40)


    ' Message subject
    Subj = "Work Roster"

    ' Compose the message

    Msg = ""
    Msg = Msg & "Dear " & Cells(ActiveCell.row(), 1) & "," & vbCrLf & vbCrLf
    Msg = Msg & "Here's your schedule:" & vbCrLf & vbCrLf

    For Each cell In Selection
    Msg = Msg & Cells(2, cell.Column) & " " & Cells(3, cell.Column) & vbCrLf
    Msg = Msg & cell & vbCrLf
    Msg = Msg & "Start time: " & Format(Cells(cell.row + 1, cell.Column), "hh:mm") & vbCrLf
    Msg = Msg & "End time: " & Format(Cells(cell.row + 2, cell.Column), "hh:mm") & vbCrLf & vbCrLf

    Next cell



    Next r
    End Sub
     
  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    OK, don't do that again then. :D

    Apologies if I over-estimated your knowledge of VBA (from the previous info that you know how to create userforms).

    1. Don't panic.

    2. Before you do anything else, please explain "the email address ... (it's sitting in cell ref A40)"
    (I don't see anything in A40 on any sheet).
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    OK, here's your file back. WARNING: I have dumped many of the month sheets to cut the size because I'm too lazy to zip it

    To display the "Macro" dialog, press ALT+F8. You should see 3 macros listed -- "QueryEmail" was added by me. To run it, either:

    click on it in the list, then click on "Run"

    or:

    just doubleclick on it in the list.

    I added an "Email" field on the Contact_Details sheet. When you run the macro, it takes the value from column A of the active cell row & looks for a match in column A on Contact_Details.

    So, if you select a cell in row 8 on March 06, you'll see a message "Mail address error." -- because "Helmut" in March 06!A8 has no match in column A on Contact_Details (Contact_Details!A7 has "Helmutt" with 2 "t"s).

    However, select a cell in row 10 on March 06 & run the macro again and the email address for "Pam" will display as a message.

    All this is about is a method for establishing the Email variable that could be used in code such as John W's. However, I won't butcher John's code indiscriminately as that might be considered "bad form" ; instead I'll hang fire until Zack's commented later.

    Rgds,
    Andy
     

    Attached Files:

  13. snoozee

    snoozee Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    259
    Thanks Andy, all i need to know now is how to put it all together (your part and Johns) (-:

    I really appreciate your help and i'm learning heaps thanks to you guys.
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,445
    Use Thread Tools to "Mark Solved" for the time being. Then PM me your email address & I'll send you an update with some basic instructions.
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/457950

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice