PDA

View Full Version : [SOLVED] How to Combine a Save As PDF macro and an E-Mail send macro???



Granoldad
12-07-2016, 05:44 PM
I have created a worksheet that has several buttons assigned to macros
One to save the file as .xlsm
One to save the file as .pdf
One to email the file .xlsm – needs to be changed to .pdf

The people I have showed this to want to have only one button. Is there anyway to combine all three in one macro so it will first save as .xlsm, and then save as .pdf, and then email the .pdf?
I cannot seem to get it right; and have tried many times???

Macro1:

Sub SaveMyWorkbook()
' SaveMyWorkbook Macro
On Error GoTo ende
Dim strPath As String
Dim strFolderPath As String
strFolderPath = Sheet1.Range("F2").Value
strPath = strFolderPath & _
Sheet1.Range("A2").Value & "-" & _
Sheet1.Range("B2").Value & "-for-" & _
Sheet1.Range("C2").Value & "-WE-" & _
Sheet1.Range("D2").Value & ".xlsm"
ActiveWorkbook.SaveAs Filename:=strPath
ende:
Set app = Nothing
Set itm = Nothing
End Sub

Macro2:

Sub SaveMyPDF()
' SaveMyPDF Macro
On Error GoTo ende
Dim strPath As String
Dim strFolderPath As String
strFolderPath = Sheet1.Range("F2").Value
strPath = strFolderPath & _
Sheet1.Range("A2").Value & "-" & _
Sheet1.Range("B2").Value & "-for-" & _
Sheet1.Range("C2").Value & "-WE-" & _
Sheet1.Range("D2").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPath
ende:
Set app = Nothing
Set itm = Nothing
End Sub

Macro3:

Sub EmailSend()
' EmailSend Macro
Dim outlookOBJ As Object
Dim mItem As Object
Set outlookOBJ = CreateObject("Outlook.Application")
Set mItem = outlookOBJ.CreateItem(olMailItem)
With mItem
.To = Range("B3").Value
.CC = Range("E3").Value
.Subject = Range("I3").Value
.Body = Range("M3").Value & vbCrLf & vbCrLf & Range("N3")
.Attachments.Add ThisWorkbook.Path & "\" & ThisWorkbook.Name
.Send
End With
End Sub

Kenneth Hobs
12-07-2016, 06:06 PM
While you could combine it all, why not make a 4th with a call to the other 3?
.

Granoldad
12-07-2016, 08:01 PM
Yes I know how to do that with a call statement although, the pdf save exports a file that is a pdf and the email send emails a .xlsm file. I need the email send to email a .pdf file.
I keep trying to change the macro3 to something like:

.Attachments.Add ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPath

I keep getting hosed up trying to get the strPath lines from macro2 to work correctly in macro3?

Kenneth Hobs
12-07-2016, 08:27 PM
While you could make StrPath as a global variable, it is just as easy to rebuild it in your other routine or to pass it as input to your email Sub. Or just combine them. I normally do that.

ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath
.Attachments.Add strPath

Granoldad
12-08-2016, 07:33 AM
Please let me know if this looks correct. I created another macro and copied the pdf save and email send macros into it and updated them.
Seems to work properly, saves as pdf and sends as attachment in outlook.

Sub PDFandSEND()
'
' PDFandSEND Macro
'
' SaveMyPDF Macro
'
On Error GoTo ende

Dim strPath As String
Dim strFolderPath As String
strFolderPath = Sheet1.Range("F2").Value
strPath = strFolderPath & _
Sheet1.Range("A2").Value & "-" & _
Sheet1.Range("B2").Value & "-for-" & _
Sheet1.Range("C2").Value & "-WE-" & _
Sheet1.Range("D2").Value & ".pdf"

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strPath

ende:
Set app = Nothing
Set itm = Nothing



'
' EmailSend Macro
'
Dim outlookOBJ As Object
Dim mItem As Object
Set outlookOBJ = CreateObject("Outlook.Application")
Set mItem = outlookOBJ.CreateItem(olMailItem)
With mItem
.To = Range("B3").Value
.CC = Range("E3").Value
.Subject = Range("I3").Value
.Body = Range("M3").Value & vbCrLf & vbCrLf & Range("N3")
.Attachments.Add strPath
.Send

End With
End Sub

Kenneth Hobs
12-08-2016, 07:56 AM
Looks like it should work. Of course there is nothing wrong setting file extensions along with fileformats but they must match the fileformat's requirement.

Granoldad
12-10-2016, 07:09 PM
Thanks Kenneth