Not sure how to go about this.... I have code to print excel to pdf and email. In order to test I had to hard code the filename when it looked for the file to attach how do I have it capture the filename that is created when it is saved? Horrible with loops/if statements I need to email to different depending on the value in B4 if B4 = ABC email@example.com and firstname.lastname@example.org, B4 = DEF email@example.com
Dim fname As String
'create filename from workbookname, sheetname and todays date
fname = ActiveSheet.Range("b4").Value & "-" & ActiveSheet.Name & "-" & ActiveSheet.Range("b6").Value & Format(Date, " YYYY.MM.DD")
'change path to suit yourself.. currently points to "c:\temp\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\users\jmac001\Desktop\" & fname, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
Set Mail_object = CreateObject("Outlook.Application")
.Subject = "Forecast Updates"
.To = ""
.Body = ""
.Attachments.Add "C:\users\jmac001\Desktop\TestFile 2013.05.24.pdf"