VBA to Send Email Requests from Master List Array

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!

Attachment 2106

Welcome to the forum!

In a Module:

Code:

---

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

---

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

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!

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.