Automating emails and content from excel via outlook

Hi Tom,

I have a few questions before I present you with a solution:

• How are the variables presented on your spreadsheet (i.e first name, company name, and country)?

• Will there be any attachments with the e-mail being sent to your clients?

• What would you like the subject to be? (i.e Product/Company Name - Date)

• Do you want to pre-fill the body of your text using certain variables from the spreadsheet?

• Will you be sending CC or BCC to a specific person(s)?

• Do you have a column containing the list of e-mails for your clients?

• Would you like to review the e-mail before mailing, or would you like it to send it automatically?

Hello Tom,

Here is a solution based on your request. Please bear in mind that a few assumptions have been made in writing your code. Any alterations in the following will cause the code not to function correctly:

1) Columns are in the following order starting in Row 1:

1. Company
2. First Name
3. Email Address
4. Country
5. Product
6. Offer
7. Valid Date

2) The worksheet containing your data must be called "Clients"

Option Explicit

Public Sub Mailing_List()
    Dim wsClients As Worksheet
    Dim dataArr() As Variant
    Dim company As String
    Dim firstName As String
    Dim email As String
    Dim country As String
    Dim product As String
    Dim offer As String
    Dim validDate As Date
    Dim currDate As Date
    Dim rows As Integer
    Dim i As Integer
    
    currDate = Now()
    Set wsClients = ThisWorkbook.Sheets("Clients")
    dataArr = wsClients.Range("A1").CurrentRegion
    rows = UBound(dataArr, 1)
    
    'Send a generic email to a list of clients from your "Clients" spreadsheet
    For i = 2 To rows
        company = dataArr(i, 1)
        firstName = dataArr(i, 2)
        email = dataArr(i, 3)
        country = dataArr(i, 4)
        product = dataArr(i, 5)
        offer = dataArr(i, 6)
        validDate = dataArr(i, 7)
        Call Mail(company, firstName, email, country, product, offer, validDate, currDate)
    Next i
End Sub

Private Function Mail(company, firstName, email, country, product, offer, validDate, currDate)
Application.ScreenUpdating = False
    Dim OutApp As Object
    Dim OutMail As Object
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        
        On Error Resume Next
        With OutMail
            '+---------------------------------------------------------+
            ' You can use the following variables to modify your e-mail:
            '
            ' company       Column A
            ' firstName     Column B
            ' email         Column C
            ' country       Column D
            ' product       Column E
            ' offer         Column F
            ' validDate     Column G
            ' currDate
            '+---------------------------------------------------------+
            .To = email
            .CC = ""
            .BCC = ""
            .Subject = company & " - " & Format(currDate, "mmmm dd, yyyy")
            .HTMLBody = "<font face=calibri color=black>" & _
                        "Hello " & firstName & "," & _
                        "<p>" & "Line 1 *********************************************************************" & "</p>" & _
                        "<p>" & "Line 2 *********************************************************************" & "</p>" & _
                        "<p>" & "Line 3 *********************************************************************" & "</p>" & _
                        "Best Regards," & _
                        "<br>" & "Your Name" & _
                        "<br>" & "Your Title" & _
                        "<br>" & "Your Address" & _
                        "<br>" & "Phone: " & "123456" & _
                        "<br>" & "Cell: " & "123456" & _
                        "<br>" & "Fax: " & "123456" & _
                        "<br>" & "E-mail: " & "<a mailto=Texastom123@excelforum.com>" & "Texastom123@excelforum.com" & "</a>" & _
                        "<br>" & "Website: " & "<a href=www.excelforum.com>" & "http://www.excelforum.com/" & "</a>"
            .Send
        End With
Application.ScreenUpdating = True
End Function