Save worksheets as separate PDFs and then e-mail them to separate recipients

[bjacobs]

Hello gurus,

I am new to the forum and new to forums in general (I know, what rock did I crawl out from?)

Anyway, I'm not too strong in VBA, but I'm decent with Excel.

I have a workbook with multiple worksheets in it. Each worksheet is a person's name, and each worksheet has sensitive data for only that person. One of the cells in each worksheet is the person's e-mail address (cell B37).

Every 2 weeks, I paste a large batch of data onto a separate sheet in the WB and the WS's automatically calculate all of the data.

I need to create a macro to automatically save each worksheet as a separate PDF (worksheet name as the .PDF is fine) and then automatically send each of those PDFs to the recipients as listed in each of the worksheet's e-mail address cell.

I've searched Google and this forum, and I built some of the pieces gradually. So far, my code saves all of the .PDF files, but I can't figure out how to connect the Outlook portion properly.

I appreciate any help in advance, and I hope that I have followed all of the forum rules.

I am aware that the portion "myattachments.Add ActiveWorkbook.Path & "" &" is incomplete. I doubt it's even proper here, but I was stumped, so here I am.

My code is below:

Sub sendReminderMail()
ChDir "C:\Users\jacobsb\Desktop\2018 DC Dashboards"


Dim strPath As String
Dim wks As Worksheet


strPath = ActiveWorkbook.Path & "\"


For Each wks In ActiveWorkbook.Worksheets
    wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"
Next wks


Dim Outlookapp As Object
Dim Outlookmailitem As Object
Dim myattchments As Object


Set Outlookapp = CreateObject("Outlook.application")
Set Outlookmailitem = Outlookapp.createitem(0)
Set myattachments = Outlookmailitem.attachments


With Outlookmailitem
.To = [b37]
.Subject = "Distribution Doc"
.Body = "Attached is the Doc"
myattachments.Add ActiveWorkbook.Path & "\" &
'.send
.Display
End With


Set Outlookmailitem = Nothing
Set Outlookapp = Nothing


End Sub