VBA, Userform, Send Email, Checkbox Select Sheets to Email as PDF

[zeheckman]

I have this code, not sure how to post in the correct code format, but here is what i am looking to do.

I am looking to take this code, make it so that I can have 4 checkboxes in my Userform. when any one of those 4 check boxes are selected it will attach a certain sheet (named the same as the check boxes) from my excel workbook.

I dont need it to save a hard copy of the PDF on my computer, just need it to attach it and then send the PDF on the email. I dont want the excel sheet to get out due to sensitive information.

here is the code.


Private Sub CommandButton1_Click()


ActiveWorkbook.Save

Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object

'Title
Title = "Add Title Here"

' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, "1")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "" & ".pdf"
Wkbk = ActiveWorkbook.Path & "/" & ActiveWorkbook.Name
' Export activesheet as PDF
With ThisWorkbook
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0

' Prepare e-mail with PDF attachment
With OutlApp.CreateItem(0)

' Prepare e-mail
.Subject = Title
.To = TextBox4.Value
.CC = TextBox1.Value
.BCC = "XXXX@XXXX.COM"
.Body = "Email Body Title." & vbLf & vbLf _
& "This Quote was Sent to you From: " & Application.UserName & vbLf & vbLf & TextBox2.Value & vbLf & vbLf & vbLf _
& "Our Corporate Values:" & vbLf _
& "Email Signature" & vbLf & "Email Signature 2" & vbLf & vbLf

.Attachments.Add PdfFile

x = TextBox3.Value
If x <> "" Then
Spx = Split(x, " ; ")
For k = 0 To UBound(Spx)
If Trim(Spx(k)) <> "" Then

.Attachments.Add Trim(Spx(k))
End If
Next
End If

' Try to send
On Error Resume Next
.Send
Application.Visible = True
If Err Then
MsgBox "Message NOT sent. Try again!", vbCritical
Else
MsgBox "Your Rental Quote Has Been Sent To " & TextBox4.Value, vbOKOnly
End If
On Error GoTo 0

End With

' Delete PDF file
Kill PdfFile

' Quit Outlook if it was created by this code
If IsCreated Then OutlApp.Quit

' Release the memory of object variable
Set OutlApp = Nothing
Unload Me
End Sub

here is the userform, this code is in the command button Send Email.


-- removed inline image ---

 

[zeheckman]

The userform has the following.

To Textbox (to type the email into)
CC textbox (for CC)
Message Text Box (for any message or notes that need to be included in the email.
Additional Attachments textbox (textbox 3, Locked, but used for file location)
4 checkboxes, 1 for each sheet i want to have for selecting
Attach external doc with its own button (Linked to textbox 3)
Send Email Button with the code above in it.

 

[zeheckman]

Answer Solved using another Forum Page and changing that text to work.

http://www.mrexcel.com/forum/excel-...excel-pdf-email-attachment-7.html#post3986527

Thanks Everyone!