Hello!
I have completed a media traffic template that assists the user in providing traffic instructions to up to 12 stations.
Checkboxes are used to determine which stations in the list are currently in use so the user may only select 3 or 4 of the 12, etc.
The selected checkbox stations have their individual instructions saved automatically by:
1. First checking to see if the station checkbox is selected or not, and then
2. Saving the form (by station for only those checkboxes that are active) in a PDF format to a predetermined place on a file server using fields in the form to dictate the subdirectory and file name.
But I thought it was even "more cool" to generate an email at the end of this process with LINKS to the appropriate PDF documents that are created. My email works and so does the first link so I feel I'm close.
Where I am stuck is in adding additional links after checking to see if the checkbox is active for each station. I need a nudge in the right direction. Can you help? Here is what I have:
Sub Initiate_Mail_Notifications()
' This Sub Macro is designed to initiate an Outlook email from the user to the Support Team indicating that New Traffic Sheets are complete and ready for them to send out to the stations.
'
' This Macro sends the links to the last saved versions of the Traffic Sheet objects.
'
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 = "traffic@mycompany.com"
.CC = ""
.BCC = ""
.Subject = "Traffic Instructions for the " & Range("client1text") & " " & Range("market1") & " market are processed and ready to distribute"
.Body = "Hello. The following Traffic Instructions are ready to distribute" & vbNewLine & vbNewLine
NOTE: THIS IS WHERE I'M STUCK!
ActiveSheet.Shapes.Range(Array("Check Box 100")).Select
If Selection.Value = xlOn Then
"Click Here <File:\K:\Station Traffic\" & Range("A1") & " TRFC\" & Range("A1") & " TRFC " & _
Range("A2") & "\" & Range("A1") & " TRFC " & Range("A2") & " " & Range("A3") & " " & _
Range("A4") & "\" & Range("A5") & " TRFC " & Range("A2") & " " & _
Range("A3") & " " & Range("A4") & "\" & Range("A6") & " TRFC " & _
Range("A2") & " " & Range("A3") & " " & Range("A7") & ".pdf>"
ElseIf Selection.Value = xlOff Then
End If
NOTE: FROM HERE, I WANT TO ADD A COUPLE OF BLANK LINES AND TEST THE NEXT CHECKBOX, INSERT THE NEXT LINK, AND SO ON...
.display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Again - the link I have above works if inserted by itself with no test for the checkbox.
I just need to find a way to assess each checkbox and add each additional link if the checkbox is active.
Thank you!
Patrick
Bookmarks