ITCooky Recipes

Lets cooky it yammy things!

How to send multiple emails from Excel with personalized content!

дата April 28, 2020

It is an atypical task, but it happens: you have to send 100 users their 100 new passwords, and how, by hand or something like that? If you have an office suite with Excel and Outlook in Windows (well I don’t have one), you can do it from an Excel sheet, I have a solution, almost, easy and elegant!

You must first activate Macros in Excel and add the Developer tab to ribben – find out how by itself, here I have a picture about Macros

I forgot, when I had Windows, take a screenshot of the Developer tab, there is a Macros icon on the left, click on it and add a macro like this

Code of macros:

Sub Create_Mail_From_List()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    
Dim strbody As String
For Each cell In Worksheets("Text").Range("E10:E12")
    strbody = strbody & cell.Value & vbNewLine
Next
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Worksheets("Data").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Worksheets("Data").Cells(cell.Row, "D").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = Worksheets("Text").Range("E6") & " " & Worksheets("Data").Cells(cell.Row, "C").Value & " " & Worksheets("Text").Range("G6")
               .Body = Worksheets("Text").Range("E7") & " " & Worksheets("Data").Cells(cell.Row, "C").Value & " " & Worksheets("Text").Range("G7") & vbNewLine & Worksheets("Text").Range("E8") & vbNewLine & Worksheets("Text").Range("E9") & " " & Worksheets("Data").Cells(cell.Row, "E").Value & " " & Worksheets("Text").Range("G9") & vbNewLine & strbody

                'You can also add files like this:
                '.Attachments.Add ("C:\test.txt")
                .Send  'Or use Display.
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

We make the tabs, it is important how they are called and in which position they are

First tab Texto

Here in certain places the text is inserted, which will be used in the letter of the mail.

Second tab Data

The most important tab in this exel table – vlook collects the information from other tabs, and it turns out that Macros doesn’t know how to cooperate with formulas in the table, can’t take the result!

In the B column, take the ID from the A column and look for the email in the Contacts tab.

=VLOOKUP(A1; $Contacts.$A:$E;2;0)

IMPORTANT: VLOOKUP can only search from left to right. It takes what is on the left and find what is on the right

In the C column, we take the ID from the A column and look for the name in the Contacts tab.

=VLOOKUP(A1; $Contacts.$A:$E;3;0)

In the E column, take the ID from the A column and search for the word in the Word_List tab.

=VLOOKUP(A1; $Word_List.$A:$B;2;0)

In the D column if it is yes , the macro sends an email; otherwise they will not be shipped

Third tab Contacs

Contact details here

Fourh tab Word_List

Here id and words

How to make VBA macros work with VLOOKUP on a table
Until now, there is no way, but there is an embarrassing trick that still allows you to fulfill your plan!
In the Data tab, you must copy all the information and enter it immediately as values so that the formulas disappear.

After that, on the Developers tab, you need to click on the second icon on the left and run the macro. The result of the work is seen in a screenshot at the beginning of the article (yes, this is a real screenshot)


Leave a Reply

Your email address will not be published. Required fields are marked *