jmac001
asked on
Excel to PDF and Outlook
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 joe.cool@somewhere.com and john.doe@somewhere.com, B4 = DEF jane.doe@somewhere.com
Sub PrintEmail2()
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:= _
False
Set Mail_object = CreateObject("Outlook.Application")
With Mail_object.CreateItem(o)
.Subject = "Forecast Updates"
.To = ""
.Body = ""
.Attachments.Add "C:\users\jmac001\Desktop\TestFile 2013.05.24.pdf"
.send
End With
End Sub
ASKER
Recieve a Outlook message box: A program is trying to send an email message on your behalf..... and has Allow/Deny/Help buttons, can this be coded to allow the email to be sent and the user not have to click allow?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did find some info on the message box it is a security alert, for now not going to disable will have the user allow. Thanks for all your help.
Open in new window