I have a training tracker in excel and I have a command button that will automatically open outlook and create the email reminder for expired training when pressed. However, I am having an issue with it auto-populating the contents of the column for which the email addresses are listed. My goal is to use the filter function to filter by expired dates and then drag select to highlight all the email address I want to send the reminder to, then click the command button and it pulls up 1 email with all the selected cell's addresses in the TO box. When I drag to select, it only populates the first cell I touch before dragging into the TO box in outlook. A bonus but not a necessity would be if I could get this email to automatically attach a PDF document to it :) I am very new to this and actually copied this command button email code from someone else. Thank you very much for your help! This is the code:
Private Sub CommandButton1_Click()
'Updated by Extendoffice 2017/9/14
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hello Sir/Ma'am," & vbNewLine & vbNewLine & _
"You are receiving this email because your Government Travel Card Statement of Understanding and Training Certificate has or is about to expire. The Training is required to be accomplished every three years and the Statement of Understanding resigned every three years and upon arrival at each new duty station." & vbNewLine & vbNewLine & _
"Attached is the SoU that needs to be completed and signed by both the member and the member's supervisor. Please follow the below steps to access the Training:" & vbNewLine & vbNewLine & _
"-Go to the TRAX website: https://www.defensetravel.dod.mil/Passport/bin/Passport.html" & vbNewLine & vbNewLine & _
"-CAC Login" & vbNewLine & vbNewLine & _
"-Click the Training Icon at the top of the page" & vbNewLine & vbNewLine & _
"-Select the View All radio button" & vbNewLine & vbNewLine & _
"-Select Launch for the Programs & Policies - Travel Card Program (Travel Card 101) [Mandatory] Course" & vbNewLine & vbNewLine & _
"-When completed, format certificate to Landscape and save as PDF" & vbNewLine & vbNewLine & _
"Once both documents are complete, respond to this email with both documents attached so we can update our records. This is mandatory and failure to accomplish can result in temporary closure of your GTC account." & vbNewLine & vbNewLine & _
"If you have any questions or concerns please let me know." & vbNewLine & vbNewLine & _
"Thank you"
On Error Resume Next
With xOutMail
.To = Selection.Value
.CC = ""
.BCC = ""
.Subject = "GTC SoU & Training Certificate Expired"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub