Good evening,
I am working on a Macro that is going to compile a form into a PDF and email it to a person within our institution. I currently have it working correctly to generate the email and get the active sheet turned into a PDF, but I need the PDF to also include another workbook that is full of upfront data. Is there a way/code that I can use to generate a PDF for two open workbook tabs? If it helps, the first workbook will always be called 'Materials Selection', and then the active page will be one of several choices.
Here is the code that I am currently using, can this be modified to achieve what I have above?
Sub sendReminderMailConsult()
ChDir "C:\Users" & Environ("Username") & "\Desktop"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users" & Environ("Username") & "\Desktop\Materials Request Form_" & Format(Now, "mm-dd-yyyy_hhmm") & ".pdf", OpenAfterPublish:=True
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = "Insert.name@institution.edu"
.Subject = "Materials Selection Form Submission"
.Body = "Please see the attached materials selection form for your review."
myAttachments.Add "C:\Users" & Environ("Username") & "\Desktop\Materials Request Form_" & Format(Now, "mm-dd-yyyy_hhmm") & ".pdf"
'.send
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
Thank you for your help!!
I am working on a Macro that is going to compile a form into a PDF and email it to a person within our institution. I currently have it working correctly to generate the email and get the active sheet turned into a PDF, but I need the PDF to also include another workbook that is full of upfront data. Is there a way/code that I can use to generate a PDF for two open workbook tabs? If it helps, the first workbook will always be called 'Materials Selection', and then the active page will be one of several choices.
Here is the code that I am currently using, can this be modified to achieve what I have above?
Sub sendReminderMailConsult()
ChDir "C:\Users" & Environ("Username") & "\Desktop"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users" & Environ("Username") & "\Desktop\Materials Request Form_" & Format(Now, "mm-dd-yyyy_hhmm") & ".pdf", OpenAfterPublish:=True
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object
Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)
Set myAttachments = OutLookMailItem.Attachments
With OutLookMailItem
.To = "Insert.name@institution.edu"
.Subject = "Materials Selection Form Submission"
.Body = "Please see the attached materials selection form for your review."
myAttachments.Add "C:\Users" & Environ("Username") & "\Desktop\Materials Request Form_" & Format(Now, "mm-dd-yyyy_hhmm") & ".pdf"
'.send
.Display
End With
Set OutLookMailItem = Nothing
Set OutLookApp = Nothing
End Sub
Thank you for your help!!