Concatenate Field of Emails (1 Viewer)

[randallst]

Hi Everybody,

Me again, im hoping this is my last question as I have been playing all day and lost the plot haha!

I have created a Query that lists all email addresses that need to receive an email notification when we raise an incident in the database.

The query is called Qry_NotificationEmail and the field that I want to concatenate is called Email.

I currently have vba code linked to a button to generate a pre-populated email, but i have to manually enter the email addresses either into Outlook or the vba. I want it to be able to pull from the query in the event I move on / leave etc.

We are using Outlook 365 using the Desktop Application.

Private Sub Email_Notification_Button_Click()

Dim ccto As String
Dim bccto As String
Dim mailto As String

'Mail Recipients
mailto = ""
ccto = ""
bccto = ""

'Email Subject
mailsub = "New Incident: " & Me.Incident_ID & ""

'Email Message
emailmsg = "Incident ID: " & Me.Incident_ID & vbNewLine & "Category: " & Me.Category & vbNewLine & "Customer: " & Me.Customer & vbNewLine & "Title: " & Me.Title & vbNewLine & vbNewLine & "Please liaise with the Engineering Team for further information in relation to the above incident"

On Error Resume Next
DoCmd.SendObject acSendNoObject, , , mailto, ccto, bccto, mailsub, emailmsg, True

End Sub

Any help / guidance as always is really appreciated

All the best
Stuart

P.S. If anyone fancies telling me how to add a light blue background to the above code, please feel free ha-ha. It's a nice to have that I will play with in the future.

 

[theDBguy]

Hi. See if this helps get you started.

 

[plog]

how to add a light blue background

Emails are formatted using HTML. Every method I've seen says the best way to do this is with a table and using css to style it with the appropriate background:

How to change the Background-Colors in a HTML Email Newsletter

How to change the Background-Colors for Body, Table and Cell in a HTML Email Newsletter.

www.arclab.com

 

[Micron]

Well, beat to the punch again. Seeing as how I have this air code sitting on a Notepad doc, I might as well post it as much as just delete it.

Dim rs As DAO.Recordset

Set rs = Currentdb.Openrecordset("Qry_NotificationEmail")
If Not (rs.EOF And rs.BOF) Then
  rs.MoveFirst
  Do While Not rs.EOF
    If mailto = "" Then
      mailto = rs.Fields("Email")
    Else
      mailto = mailto & ";" & rs.Fields("Email")
    End If
    rs.MoveNext
  Loop
End If

Always step through such code at first lest you end up sending a ton of emails because you get stuck in a code loop. In fact, in the past I have gotten the record count and used a counter value to ensure no more emails can be sent than the number of address records - even if it means they all get sent to the same person!

 

[Isaac]

For the light blue background, use the advice already given in this thread

Use VBA to set Email Background Colour

Hi Everybody, It's been a good while since posting on here, but hoping someone might be able to help with my following question, or if it is even possible haha! Is it possible to apply a email background colour using VBA? My current coding that works for the general message is the following...

www.access-programmers.co.uk

 

[Gasman]

FWIW I use

mailto = mailto &  rs.Fields("Email")  & ";"

Having the last character as ; does not cause any problems, and saves a little code and logic?

 

[Uncle Gizmo]

My code is similar to Micron's and is available on my website here:-

Display Field Data Horizontally - Nifty Access

Transform, Concatenate Examples A common requirement in MS Access is to extract Columnar data into horizontal data, for instance to make reports look better, to provide a list of emails suitable to send to with Outlook. In the YouTube videos below I explain how this can be done. Display Field...

www.niftyaccess.com

Along with a video explanation.

You may find this information on sending multiple emails from AWF contributor Gina Whipp helpful:-

Access Diva Send eMail to Multiple Recipients

Access-Diva provides custom databases using Microsoft Access utilizing SQL Server when called for.

www.access-diva.com

 

[Micron]

Having the last character as ; does not cause any problems,

I wasn't sure about that and didn't want to start with appending ; so I went the other way. Your advantage is that you've probably actually used this more or less recently instead of dreaming up air code.
I used CDO when I sent mail and don't recall how I treated the recipients list - it was long ago.

 

[The_Doc_Man]

I've always used a short recordset loop and just concatenated the e-mail addresses with a semi-colon as a separator.