save as pdf and email

[RedOctoberKnight]

Good Morning all,

I see that this question has been asked multiple times but I just cant seem to get it work for my needs. I found the following code online and have been slightly tweaking it using a scrap workbook. The problem is it doesn't do everything I'd like it to do. What I have is a workbook with multiple worksheets that contain payroll for that day (each sheet is it's own day). My ultimate goal is to be able to click a button and have it save a specified range on that selected sheet as a .pdf and then email it to the appropriate address. I would also like it to save each .pdf as the name in a specified cell on each sheet (lets say B1) in the following folder "H:\mci-sup\Excel\STAFFING\Daily Exception logs-SENT" As always, any help would be much appreciated.

Sub Send_Email()

Range("N37").Value = Application.UserName

    Dim wPath As String, wFile As String
    
    wPath = ThisWorkbook.Path
    wFile = Range("A1").Value & ".pdf"
    Range("J1:Q35").ExportAsFixedFormat Type:=xlTypePDF, Filename:=wPath & wFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False


    Set dam = CreateObject("Outlook.Application").CreateItem(0)
    '
    dam.To = "email address"
    dam.cc = "email address"
    dam.Subject = "MCI PROVO EXCEPTION LOG - " & Range("A1").Value
    dam.Body = "See attached"
    dam.Attachments.Add wPath & wFile
    dam.display
    'MsgBox "Email sent"


End Sub