VBA to Send Email Requests from Master List Array

[pkearney10]

Hello,

I often need to email the same companies multiple times requesting catalog update files for their various subsidiaries. I would like to create a macro that can compare my request list to a master list, match the email contact from that master list, and compose an email to that contact for each request. I've attached an example.

Request List

Column A - Company to match to master list
Column B - Name of subsidiary that I need the catalog file
Column C - If "NO" then send email. If "YES" then skip
Column D - Return the date/time email was sent
Column E - Does Column A in Request List match anything in Column A of Master List? If "FALSE" then skip.

Master List

Column A - Company name
Column B - Contact First Name
Column C - Contact Last Name
Column D - Contact Email
Column E - Contact CC (if populated)
Column F - Additional CC

To summarize, I want the macro to Match Column A from "Request List" to column A from Master list. If there's a match, create an email based off of the criteria in Master List. Email should say:

Subject: Catalog Request: (Request List Column B)

Hello (Master List Column B),

May you please send the Subsidiary Catalog List for (Request List Column B)?

Thank you,

-Signature

From here, the Request List should flip Column C from "NO" to "YES" and column D should populate the date/time sent.

Anyone have any suggestions or articles? I've found a bunch that do bulk mailings, but nothing that will match against a master list.

Thanks!

2106

[Kenneth Hobson]

Welcome to the forum!

In a Module:

Sub Main()
Dim c As Range, f As Range, source As Worksheet, master As Worksheet, s As String
'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
Dim olApp As Outlook.Application, olMail As Outlook.MailItem

Set source = Worksheets("Request List")
Set master = Worksheets("Master List")
Set olApp = New Outlook.Application

For Each c In source.Range("A2", source.Cells(source.Rows.Count, "A").End(xlUp))
With c
If .Offset(, 2).Value <> "NO" Then GoTo NextC
If .Offset(, 4).Value <> True Then GoTo NextC

Set f = master.Range("A:A").Find(c.Value)
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = f.Offset(, 3).Value 'Master Column D
.CC = f.Offset(, 5).Value 'Master Column E
.Subject = "Catalog Request: " & c.Offset(, 1).Value 'Source Column B
'Build body string:
s = "Hello " & f.Offset(, 1).Value & "," & vbCrLf & vbCrLf
s = s & "May you please send the Subsidiary Catalog List for " & _
c.Offset(, 1).Value & "?" & vbCrLf & vbCrLf
s = s & "Thanks you," & vbCrLf & vbCrLf
s = s & "sig data..."
.Body = s
.Display
'.Send
End With
.Offset(, 2).Value = "YES" 'Source sheet sent, YES.
.Offset(, 3).Value = Date 'Source sheet, Date sent.
End With
NextC:
Next c

On Error Resume Next
Set olMail = Nothing
Set olApp = Nothing
End Sub

[pkearney10]

Incredible! Exactly what I was looking for! Thank you so much!

[pkearney10]

Hi Kenneth

Is there a way to add my default signature to the macro? It's an HTML signature with a photo. I tried using .HTMLBody but when I do that the body text doesn't use my default fonts for some reason...

Thanks!

[Kenneth Hobson]

Use HTML to set your fonts. IF you don't know html, you could put your text into an Excel range and then use Ron de Bruin's RangeToHTML().
https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

He has a routine to use the signature html file as well.
https://www.rondebruin.nl/win/s1/outlook/signature.htm

There are 2 other ways to insert the signature.