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