Excel VBA Macro: Email PDF Tailored for Specific Recipient(s) Based on Cell Value

Video Description

💼 Hire Me for Excel VBA Automation Need help automating your Excel workflows? I specialize in building custom Excel VBA solutions to save you time and eliminate repetitive tasks. 👉 https://www.upwork.com/freelancers/~013bd05e311ebf4a2c Excel VBA Macro: Email PDF Tailored for Specific Recipient(s) Based on Cell Value 💥Subscribe: https://youtube.com/@greggowaffles Code: Sub send_pdf_based_on_recipients() Dim OutApp As Object Dim OutMail As Object Dim ws1 As Worksheet Dim ws2 As Worksheet Dim pdf_range As Range Dim pdf_path As String Dim selection As String Dim subject As String Dim sendTo As String Dim rec_count As Integer Dim row_count As Integer Dim col_count As Integer Dim i As Integer Dim j As Integer Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) Set ws1 = ThisWorkbook.Sheets("Recipients") selection = ws1.Cells(1, 2).Text If selection = ws1.Cells(3, 1).Text Then i = 1 Set ws2 = ThisWorkbook.Sheets("Sheet1") ElseIf selection = ws1.Cells(3, 2).Text Then i = 2 Set ws2 = ThisWorkbook.Sheets("Sheet2") Else i = 3 Set ws2 = ThisWorkbook.Sheets("Sheet3") End If ws2.Activate row_count = ws2.Cells(Rows.count, 1).End(xlUp).Row col_count = ws2.Cells(1, Columns.count).End(xlToLeft).Column Set pdf_range = ws2.Range(Cells(1, 1), Cells(row_count, col_count)) ws1.Activate subject = ws1.Cells(3, i).Text & " Most Populous Countries in the World! " rec_count = ws1.Cells(Rows.count, i).End(xlUp).Row For j = 4 To rec_count sendTo = sendTo & ws1.Cells(j, i).Text If j ＜＞ rec_count Then sendTo = sendTo & "; " End If Next j pdf_path = "C:\Users\greggowaffles\Documents\Youtube Videos\Test\Stats\" & _ selection & " Population Data.pdf" 'align range in pdf ws2.PageSetup.CenterHorizontally = True pdf_range.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=pdf_path, OpenAfterPublish:=False 'create email 'On Error Resume Next With OutMail .To = sendTo .CC = "" .BCC = "" .subject = subject & Format(Date, "mm-dd-yy") .Display ..HTMLBody = "＜BODY style = 'font-size:12pt; font-family:Calibri'＞" & _ "Hi Team,＜p＞Please see attached pdf file.＜p＞" & _ "Thanks,＜br＞Greg" & .HTMLBody .Attachments.Add pdf_path End With On Error GoTo 0 Set OutApp = Nothing Set OutMail = Nothing Kill pdf_path End Sub #excelvba #excelmacro #excelvbatutorial