Hi. I have created a userform with checkboxes to select which sheets of my workbook must be saved to PDF and emailed. Currently the code is working, but the only problem is that not only the selected sheet is saved but all are saved. I need help on how to change my code for only the selected sheets to be saved and emailed.
Form.jpg
Private Sub CommandButton1_Click()
If CheckBox1 = True Then
Sheets("Confirm").Visible = True
Else
End If
If CheckBox2 = True Then
Sheets("Work Order").Visible = True
Else
End If
If CheckBox3 = True Then
Sheets("Material").Visible = True
Else
End If
If CheckBox4 = True Then
Sheets("E-Plan").Visible = True
Else
End If
Sheets(Array("Confirm", "Work Order", "Material", "E-Plan")).Select
Sheets("Confirm").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Environ("Userprofile") & "\Documents\Emailed Jobs\" & Sheets("Confirm").Range("BA1") & ".pdf", quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Sheets("Confirm").Range("BA2") & Sheets("Confirm").Range("BA3") & Sheets("Confirm").Range("BA4") & Sheets("Confirm").Range("BA5")
.CC = Environ("Username") & "@xxxxxx.co.za"
.BCC = "xxxxxx@xxxxxx.co.za"
.Subject = "Job ready for Execution" & " " & Sheets("Confirm").Range("BA1")
.Body = "Hi." & vbNewLine & vbNewLine & "Please execute the job as per starting and completion dates." & vbNewLine & vbNewLine & "Regards" & vbNewLine & vbNewLine & "CR Section"
.Attachments.Add (Environ("Userprofile") & "\Documents\Emailed Jobs\" & Sheets("Confirm").Range("BA1") & ".pdf")
.Send
MsgBox ("Congratulations!" & vbNewLine & vbNewLine & "The job has been saved to PDF and E-mailed")
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Range("AU15").Select
Sheets("Confirm").Select
End Sub
Bookmarks