VBA PDF and Mailto Help!!

[delts509]

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!!

 

[Momentman]

Here are two ways

Worksheets(Array("Materials Selection", "NewSheet")).Select

or

Worksheets(Array("Materials Selection", ActiveSheet.Name)).Select

So your code might look like this

Sub sendReminderMailConsult()
Worksheets(Array("Materials Selection", ActiveSheet.Name)).Select
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

Although it does look like you are missing some slashes, but if it works for you though 'cos code would be

"C:\Users\" & Environ("Username") & "\Desktop"

 

[delts509]

That actually worked perfectly for me (I adjusted the form name in the VBA), but I sent it to a coworker to try (she is on a Mac), and she is receiving an error when the macro runs. The error she receives says the following:

The Macro "Macintosh HD:Users:JaneSmith:Library:Caches:TemopraryItems:Outlook Temp:Large Cost Materials Evaluation Form[1].xlsm'! sendreminderMailconsult cannot be found.

Here is the full code that I am using:

Sub sendReminderMailConsult()
Worksheets(Array("Materials Selection", ActiveSheet.Name)).Select
ChDir "C:\Users" & Environ("Username") & "\Desktop"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users" & Environ("Username") & "\Desktop\Large Cost Materials Evaluation 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 = "John.Smith@Collegestation.edu"
.Subject = "Materials Selection Form Submission"
.Body = "Please see the attached materials selection form for your review."
myAttachments.Add "C:\Users" & Environ("Username") & "\Desktop\Large Cost Materials Evaluation Form_" & Format(Now, "mm-dd-yyyy_hhmm") & ".pdf"
'.send
.Display
End With


Set OutLookMailItem = Nothing
Set OutLookApp = Nothing




End Sub

Any thoughts on what I am doing wrong here?

Thank you!!

 

[Momentman]

Glad it worked on the windows

I don't use a MAC, so i really cant say.

How are the file paths on MACS, similar to windows? Thats what you need to check