Consulting

Results 1 to 16 of 16

Thread: Added (jobs): Automating Emails from Excel

  1. #1
    VBAX Regular
    Joined
    Jun 2004
    Posts
    12
    Location

    Cool Added (jobs): Automating Emails from Excel

    hello listers
    to begin with I have no great excel skills

    What I am looking for is a macro that I can easily use to send mail to a list of users. I have made a web page that students sign up on: http://biosciences.utoledo.edu/prs, enter there info and, when they press submit, the data is saved to a text file that can be opened as a CSV file in Excel. I would like to have a macro go row by row, down the sheet, and extract the email from column G and insert that into a new email message, and also grab the students name from coulmn B. The CSV file is set up like this:
    ID Fname Lname StudentID TransmitID Section email lecture DATE

    Soon, we will add more columns to it to put student grades. Is it possable that the macro could also add up there grades and if they have an "A" it would send a msg saying that you're doing great or some thing or if they got a "D" it would say you might want to get help in class so the students will know how they're doing. Is this possable ??

    Dale

  2. #2
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, Dale!

    Do you have Microsoft Word, and if so, is there some reason you don't just use the mail merge feature for at least that portion of your task?
    ~Anne Troy

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Posts
    12
    Location
    I didn't think about mail merge. The main reason this needs to be simple to use is that the professors here at the university where I work won't use anything new in their class if it is more than a few mouse clicks--that is why I was wanting something that I could attach to a button they could push and the script would do it all for them. We are going to start using educue system in the fall (www.educue.com) and it is mainly based on Excel. If the Prof's had to use mail merge, they just won't do it, and the only people to suffer are the students. We will have somewhere around 3,000 students in this buliding alone using it, so that is a lot of emailing

  4. #4
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    What email program are you using?
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  5. #5

    Q241498 - XL2000 How to Send Form Mail That Uses Excel Data

    I worked on something similar awhile back and saved this info from MS knowledge Base.

    Q241498 - XL2000 How to Send Form Mail That Uses Excel Data.htm
    This article contains a Microsoft Visual Basic for Applications macro for Excel that uses Microsoft Outlook 2000 to generate and send a form e-mail to individuals that are listed in an Excel worksheet.
    This macro assumes the following:

    • E-mail addresses are in column A.
    • Names are in column B.
    • Cells A1 and B1 contain headers.

    To create the macro:

    1. Start Excel. Create a new workbook, and open the Visual Basic editor (press ALT+F11).
    2. On the Insert menu, click Module.
    3. On the Tools menu, click References.
    4. In the Available References list, click to select the Microsoft Outlook 9.0 Object Library check box. Click OK.
    5. Type the following code into the code module:


    'Set up the Outlook objects.
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Declare our global variables to be used in each subroutine.
    Dim CustomerAddress As String
    Dim CustomerMessage As String
    
    Sub MailItNow()
    'Declare our variables.
    Dim X As Integer
    Dim TempCustomerAddress As String
    'Prevent screen redraws until the macro is finished.
    Application.ScreenUpdating = False
    'Sort the addresses and names alphabetically, by the e-mail address.
    'This is REQUIRED to prevent any duplicate addresses from
    '    receiving more than one e-mail.
    Columns("A:B").Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    'Sets which row to start searching for e-mail addresses and names.
    X = 2
    'Begin looping through all the e-mail addresses in column A until
    '    a blank cell is hit.
    While Range("A" & X).Text <> ""
        'These variables will be used to search for duplicates.
        CustomerAddress = Range("A" & X).Text
        TempCustomerAddress = CustomerAddress
        'Increment X until a different e-mail address is found.
        While TempCustomerAddress = CustomerAddress
            X = X + 1
            CustomerAddress = Range("A" & X).Text
        Wend
        'Add the e-mail address to a global variable.
        CustomerAddress = Range("A" & X - 1).Text
        'Add a message with the user's name to the e-mail.
        'Customize your own message and closing here.
        CustomerMessage = Range("B" & X - 1).Text & "," & vbCrLf & vbCrLf _
        & "Thank you for trying our product!" & vbCrLf & vbCrLf & _
        "Sincerely," & vbCrLf & "ProductCo Inc."
        'Run the subroutine to send the message.
        Call SendMessage
    Wend
    End Sub
    
    Sub SendMessage(Optional AttachmentPath)
    'This is required to prevent a name which does not resolve to
        '    an e-mail address from hanging the app.
        On Error Resume Next
    ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")
    ' Create the message.
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
        ' Add the To recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add(CustomerAddress)
        objOutlookRecip.Type = olTo
        ' Set the Subject, Body, and Importance of the message.
        .Subject = "Thank You!"
        .Body = CustomerMessage
        .Importance = olImportanceHigh  'High importance
        ' Add attachments to the message.
        If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        End If
        ' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
            If Not objOutlookRecip.Resolve Then
                Exit Sub
            End If
        Next
        .Send '--- Send the message.
    End With
    'Remove the message and Outlook application from memory.
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
    End Sub
    6. On the File menu, click Close and Return to Microsoft Excel.
    7. To run the code, make sure the address database sheet is active. On the Tools menu, point to Macro, and click Macros. Click MailItNow, and then click Run.

    Hope this helps
    Len

  6. #6
    VBAX Regular
    Joined
    Jun 2004
    Posts
    12
    Location

    mark007

    I have out look and also raiden mail commercial server for smtp mail

    I will look at the code supplied by Len to see if i can use it i tried mail merging last night but with every mail i have to click on the yes button to send it if i have to do that to send out 400 mails to students every time they take a test it would not get done

  7. #7
    VBAX Regular
    Joined
    Jun 2004
    Posts
    12
    Location
    Len Piwowar i used your code but not being a coder my self i got a error on the first line
    Compile error:
    user defined type not defined
    on this line Dim objOutlook As Outlook.Application
    and have no idea what it is asking me for

  8. #8
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi Dale,

    Did you follow this critical step??:

    4. In the Available References list, click to select the Microsoft Outlook 9.0 Object Library check box. Click OK

  9. #9
    VBAX Regular
    Joined
    Jun 2004
    Posts
    12
    Location
    yes I did. I followed each step as it was written, I re-did it a few times and always got the same error.

  10. #10
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    622
    Location
    Very odd.

    Try opening a new workbook and adding the reference. Then type:

    dim olApp as

    And see if outlook appears in the intellisense popup..

    If so then try pasting the code in again.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  11. #11

    Relocate Dimension statements to ThisWorkBook

    Try This:

    1)Open the Excel file with the mail macro's
    2)Open Visual Basic editor expand the folder Microsoft Excel Objects
    3)Double click on ThisWorkBook folder in the Microsoft Excel Objects in the current VBAproject
    4)Open Module1 in modules of the project and cut the following code from the top of the module:

    'Set up the Outlook objects.
    Dim objOutlook As Outlook.Application 
    Dim objOutlookMsg As Outlook.MailItem 
    Dim objOutlookRecip As Outlook.Recipient 
    Dim objOutlookAttach As Outlook.Attachment 
    'Declare our global variables to be used in each subroutine.
    Dim CustomerAddress As String 
    Dim CustomerMessage As String
    5)Paste this into item 2 from above (ThisWorkBook folder) General - Declarations
    6) Save, close and Try
    Hope this helps
    Len
    Last edited by mark007; 06-10-2004 at 04:01 AM. Reason: Corrected tag...

  12. #12
    VBAX Regular
    Joined
    Jun 2004
    Posts
    12
    Location
    do I need to have this for headers in row A and B ???

    A = CustomerAddress
    B = CustomerMessage

    Or can I use

    A = email

    B = Fname

    I will try this out tonight when I get home.

  13. #13
    VBAX Regular
    Joined
    Jun 2004
    Posts
    12
    Location
    that took care of the error. But no mail was sent
    I have the word Email in A1 and the word Names in B1.
    I have email address in A2 and A3.
    Names are in B2 and B3.
    and in ThisWorkBook i pasted in

    '
    Set up the Outlook objects.
    Dim objOutlook As Outlook.Application 
    Dim objOutlookMsg As Outlook.MailItem 
    Dim objOutlookRecip As Outlook.Recipient 
    Dim objOutlookAttach As Outlook.Attachment 
    'Declare our global variables to be used in each subroutine.
    Dim CustomerAddress As String 
    Dim CustomerMessage As String

    is this write ?????
    Last edited by Anne Troy; 06-11-2004 at 01:47 PM.

  14. #14
    Correction:
    Move ThisWorkbookfolder Declarations, Dim statements
    Dim CustomerAddress As String
    Dim CustomerMessage As String


    To Module1 - General - Declarations (Top of Module)
    and change statement to public
    Public CustomerAddress As String
    Public CustomerMessage As String


    Dale,
    You should also be able to within reason name the headers any name you choose. In the proceedure you'll Find Sort Method with Header:=xlGuess which means:

    Header Optional Variant. Specifies whether the first row contains headers. Can be one of the following XlYesNoGuess constants: xlYes, xlNo, or xlGuess. Use xlYes if the first row contains headers (it shouldn't be sorted). Use xlNo if there are no headers (the entire range should be sorted). Use xlGuess to let Microsoft Excel determine whether there's a header, and to determine where it is, if there is one. The default value is xlNo.

    Row 1 contains Headers
    column A = E-mail Addresses
    Column B = Name Info

    After you open the csv file in excel you could insert two new columns at A and B and setup formulas and data as shown in attachment then after you move the Dimension statements as described in this post try the macro.


    Hope this helps
    Len
    Last edited by Len Piwowar; 06-11-2004 at 06:19 PM.

  15. #15
    VBAX Regular
    Joined
    Jun 2004
    Posts
    12
    Location
    I have been out of the office all week so i will try this at home tonight...

    Also can anyone tell me what a project like this would cost to have some one else do it for me.

  16. #16
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hi, Dale. Would you mind posting that in the Jobs Forum?
    Anyway, just guessing, I think you'd find it rather affordable.
    ~Anne Troy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •