Hi Guys,
Hoping someone can help point me in the right direction with the following. My skills are still growing so some advice would be welcomed. I have the attached code... working fine... basically uses a table of contact info populated by the user... and when you click on a button... creates a mail including all their addresses.. your email signature, etc. I've also attached a snapshot of the table as it appears on Sheet 15 of the workbook.
Sub MailButton1_Click()
On Error GoTo ErrHandler
' Set Outlook object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' Create email object.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
Dim myDataRng As Range
Set myDataRng = Sheet15.Range("MailAddys") ' get ids from named space
Dim cell As Range
Dim iCnt As Integer
Dim sEmail_ids As String
For Each cell In myDataRng
If Trim(sEmail_ids) = "" Then
sEmail_ids = cell.Offset(0, 0).Value ' keep the Offset (0, 0)
Else
sEmail_ids = sEmail_ids & vbCrLf & ";" & cell.Offset(0, 0).Value
End If
Next cell
Set myDataRng = Nothing ' Clear the range.
Dim Signature As String
With objEmail
.Display
End With
Signature = objEmail.HTMLBody
With objEmail
.To = sEmail_ids ' Assign all email ids
.Subject = [C5] & " - Update Mail - " & Date
.HTMLBody = "<p style='font-family:Ariel;font-size:13'>" & "Body Text" & vbNewLine & Signature
'.Display ' Display outlook message window.
End With
' Clear all objects.
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:
'
End SubFor reference.. L4 to L26 is set as a named range (MailAddys). What I would like to do is have the Q column be a Y/N flag on whether to include in the mail or not. So if there is an address in Column L.. but Q says "N" or "No" then its not included in the mail when you click the button.
Now if its overly difficult ill just tell the users to delete the people they dont want before they send as a workaround lol. But any help would be very gratefully appreciated.
Thanks
