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!

Mailing Request Macro.xlsx

[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.