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 Sub
For 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.