none
Outlook - Automated Email sending by pulling data from Excel RRS feed

  • Question

  • Hi,

    I have a Excel spreadsheet with around 500 names containing the name of the person and their email address etc. Apart from spending weeks, typing up an standard email, adding the specific names and emails from an excel spreadsheet, I have read this process can be automated using Outlook and Excel which would save me weeks.

    I am using Office 365 and largely use the Web Client however can use the Outlook client on the PC if it makes it easier. All I want to do is,

    1-Create the standard email

    2-Save it as a template

    3-Then get Outlook to run through the Excel spreadsheet, pick the 1st email in the row, then in the same row pick the name of the person and amend it in the body and send.

    4-Repeat the same process for the second row, third row....for 500 people.

    I have seen some add-ins to buy online however wasn't sure if they actually work or how robust they were hence wanted to find out from the experts what the best solution was which was easy. Microsoft pointed me to this forum.

    Monday, October 2, 2017 7:38 PM

All replies

  • Hello,

    You could create a macro in Excel and automate Outlook to send mails. 

    For example, the macro below would create a template firstly and then create several mail items from the mail template. Read Excel to get the name from A2:A3 and get the mail address from B2:B3. Then amend the email address and body and send.

    Sub CreateFromTemplate()
    Dim myOlApp As Outlook.Application
    Dim MyItem As Outlook.MailItem
    Set myOlApp = CreateObject("Outlook.Application")
    'Create template
    Set MyItem = myOlApp.CreateItem(olMailItem)
    MyItem.Subject = "Status Report"
    MyItem.To = "Dan Wilson"
    MyItem.Body = "Hi, Name"
    'MyItem.Display
    MyItem.SaveAs "D:\statusrep.oft", OlSaveAsType.olTemplate
    Dim cell As Range
    For Each cell In Range("A2:A3")
    Set MyItem = myOlApp.CreateItemFromTemplate("D:\statusrep.oft")
    With MyItem
    .To = cell.Offset(0, 1).Value
    .Body = Replace(.Body, "Name", cell.Value)
    .Send 'or use .Display to show the mailitem
    End With
    Next
    End Sub

    You could get started with:

    Automating Outlook from a Visual Basic Applications

    Automating Outlook from other Microsoft Office applications

    CreateItemFromTemplate Method

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, October 4, 2017 5:03 AM
    Moderator
  • You can visit https://docs.microsoft.com/en-us/outlook/add-ins/ to learn about how you can write an outlook addin to be able to provide buttons to trigger this when needed. To parse through the excel spreadsheet you would need to write your own service which does this.

    Alert sent from MSDN

    Thursday, October 5, 2017 8:08 PM