create bulk email from Excel file, but there is a catch

[rlleblan1]

Hello excel experts. I have a head scratcher and want to do somethin that I am not sure is even possible.

I have an excel file with thousands of names. I would like to create some way that it bulk emails the entire list of names and include the data in a couple of fields specific to that person.

Fore example:

It would compose an email for John and johns email would include date in 4 of the 30 columns from his row, it would simultaneously create the same format for susan, but it would have her rows data, etc.

Template may look like this: (as I mentioned it would auto fill and email to everyone in column "A" using the email in column "B", with something like the template below in a bulk fashion)

Hello (insert from column A2)

I wanted to let you know you have the following outstanding course:

1. Sales Delta: (C2 value) Sales Full course: (D2 value)
2. Engineer Delta: (E2 value) Engineer full course: (F2 value)
3. Architect Delta: (G2 value) Architect Full: (H2 value)
4. Technician Deltas: (I2 value) Technician full: (J2 value)

Please complete the course and let me know if you have any questions.

I am attaching an example in case it will help.

This will be a tough one I am sure.

 

[severynm]

Absolutely possible, and actually not too complex. Start with the most minimal boiler plate code to create an email from excel. For this to work, you also need to follow instructions at the bottom of the page here to enable early binding. It has no effect on how the code runs, but it makes the code easier to write (it allows intellisense) and easier to read, in my opinion at least.

Public Sub CreateEmail()
    'Create email object & populate necessary fields
    Dim OutApp As New Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    On Error Resume Next
    With OutMail
        .To = "..." 'customize as necessary
        .Subject = "..." 'customize as necessary
        .Body = "..." 'customize as necessary
        .Display '.Send 'Swap out .Display and .Send as needed
    End With
    On Error GoTo 0
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

However, we do not want to create just one email - we want to iterate over every row in the table. For that, let's provide the range of email addresses and then iterate over it.

Public Sub CreateEmail()
    Dim currentRow As Integer
    Dim numberRange As Range
    Dim emailRange As Range
    Dim rng As Range
    
    'Create email object & populate necessary fields
    Dim OutApp As New Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    Set emailRange = SheetName.Range("B1:B2") 'Customize with your sheet name and range
    
    For currentRow = 1 To emailRange.Rows.Count
        On Error Resume Next
        With OutMail
            .To = emailRange.Rows(currentRow).Value
            .Subject = "..." 'customize as necessary
            .Body = "..." 'customize as necessary
            .Display '.Send 'Swap out as needed
        End With
        On Error GoTo 0
    Next currentRow
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Next you want to build up the body of your email. There's a bunch of different ways you can do that. If you want text formatting in the email, mock up your text in HTML and use .HTMLBody instead of .Body. Otherwise, use .Body and set it equal to the string you like. We are already iterating over each row in your range, meaning you know the current row, so it is not too difficult to retrieve the values from the proper column. I'll leave you to figure that out for now. Tip: if you want to add a line break, use Chr(10), like so:

.Body = "..." & Chr(10) & "..."

Feel free to let me know if you have further questions....