Link to home
Create AccountLog in
Avatar of ianLMurdoch
ianLMurdochFlag for Luxembourg

asked on

Sending an HTML email to multiple users via Access 2007

Hi
Just a  quick question for someone more knowledgable than I with  Access.  
I have code working that runs from a form, building a query based on the form fields input.  This query is then used to generate an email distribution list and an email is then created to send to the recipients.

This works fine, however, I cannot get the email to go in HTML.  Whatever is entered into the form, is always send on one line without any line breaks. So is not really usefull.

Can anyone tell me how to set up the email to send as HTML  and as is seen when it is written into the form input field ?

Snippet of code is included as reference
where...
Sendoutlookemail is the procedure to send the email
errorHandling is an error handling procedure


thanks in advance
Private Sub Send_Mail_Click()
On Error GoTo Err_SendEmail

    Dim sTo As String
    Dim sCC As String
    Dim sBCC As String
    Dim sSubject As String
    Dim sBody As String
    Dim sAttachmentList As String
    Dim sReplyRecipient As String
    Dim oFSO As New FileSystemObject
    Dim oFS
    Dim db As DAO.Database
    Dim qryMail As QueryDef
    Dim MailList As DAO.Recordset

' this is a test to make sure the type of customer is chosen otherwise exit
'
  If Me.Choice = "" Then      ' from form
  MsgBox " You have to select a customer status "
  GoTo Exit_SendEmail
  End If
'
' You must key a semicolon between each email name
'
    sTo = ""
    sCC = ""
    sBCC = ""
    sReplyRecipient = "sales@domainname.com"
    sSubject = Me.Mess_Subject ' from form
    sBody = Me.mess_text       ' from form
'
'Path to the text file
 If Left(Me.Mail_Attachment_Path, 1) <> "<" Then
                Attachment = Me.Mail_Attachment_Path
                Attachment = 1
         Else
                Attachment = 0
 End If
'
'   does attachment exist
'
If Dir(Attachment) = "" Then
    MsgBox "Document not found. Please check path"
    Attachment = 0
End If
'
' Set up the database and query connections
'
Set db = CurrentDb                  'chosen database
' define query to be used from database
Set qryMail = db.QueryDefs("MyEmailAddresses")   
'
Set MailList = qryMail.OpenRecordset             '
' now, this is where we go through our list of addresses,
' adding them to the sCC list
'  LOOP through all the emails on the list
'
Do While Not MailList.EOF
       If MailList![Status] = Me.Choice Then
         NewEmail = MailList![EmailAddress]
         sBCC = sBCC & ";" & NewEmail
       End If
     MailList.MoveNext
   Loop
'
'send email but first check if there is an attachment
If Attachment = 0 Then   ' send without a file attachment
 Call SetupOutlookEmail(sTo, sCC, sBCC, sReplyRecipient, sSubject, sBody)
Else     ' send with a file attachment
 Call SetupOutlookEmail(sTo, sCC, sBCC, sReplyRecipient, sSubject, sBody, sAttachmentList)
End If

Exit_SendEmail:
    Exit Sub

Err_SendEmail:
    If Err.Number = -2147024894 Then 'Cannot find this file.  Verify the path and file name are correct.
        MsgBox "Email message was not sent.  Please verify the file " & sPathFile & " exists before attempting to resend the email.", vbCritical, "Invalid File Attachment"
        Exit Sub
    ElseIf Err.Number = -2147467259 Then 'Outlook does not recognize one or more names.
        MsgBox "Email message was not sent.  Please verify all user names and email addresses are valid before attempting to resend the email.", vbCritical, "Invalid Email Name"
        Exit Sub
    Else
        Call ErrorHandling(Err.Number, Err.Description, "sendmail_Click", "2-Email_clients_Monthly")
        Resume Exit_SendEmail
    End If
End Sub

----------------------------------------------
sendoutlookemail proc

Public Function SetupOutlookEmail(ByVal sTo As String, ByVal sCC As String, ByVal sBCC As String, ByVal sReplyRecipient As String, ByVal sSubject As String, ByVal sBody As String, ParamArray sAttachmentList() As Variant) As Boolean
On Error GoTo Err_SetupOutlookEmail
    
    Dim objOLApp As Object
    Dim outItem As Object
    Dim outFolder As Object
    Dim DestFolder As Object
    Dim outNameSpace As Object
    Dim lngAttachment As Long

    Set objOLApp = CreateObject("Outlook.Application")
    Set outNameSpace = objOLApp.GetNamespace("MAPI")
    Set outFolder = outNameSpace.GetDefaultFolder(6)
    Set outItem = objOLApp.CreateItem(0)

    outItem.To = sTo
    outItem.CC = sCC
    outItem.BCC = sBCC
    outItem.Subject = sSubject
    outItem.Body = sBody
    outItem.ReplyRecipients.Add sReplyRecipient
    outItem.ReadReceiptRequested = False

    With outItem.Attachments
        For lngAttachment = LBound(sAttachmentList) To UBound(sAttachmentList)
            .Add sAttachmentList(lngAttachment)
        Next lngAttachment
    End With

 '   outItem.Send
    outItem.Display 
'setup and open email in edit mode instead of sending the email
    SetupOutlookEmail = True

Exit_SetupOutlookEmail:
    On Error Resume Next
    Set outItem = Nothing
    Set outFolder = Nothing
    Set outNameSpace = Nothing
    Set objOLApp = Nothing
    Exit Function

Err_SetupOutlookEmail:
    If Err.Number = 287 Then 'User stopped Outlook from sending email.
        MsgBox "User aborted email.", vbInformation, "Email Cancelled"
        Resume Exit_SetupOutlookEmail
    Else
        Call ErrorHandling(Err.Number, Err.Description, "SetupOutlookEmail module", "Function in Main_window_Open_Module")
        Resume Exit_SetupOutlookEmail
    End If

End Function

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
We have an app which sends mail via SMTP rather via outlook.  
With our approach,  we have to format the email text as html.    i.e. with all the correct HTML tags.  
If you using text entered in a text box as your email body,  you would need to preprocess it first and add it HTML tags.

e.g. replace vbCRLF characters with <br/>

I would suggest you create a piece of HTML text using word or something like that,  paste the HTML into your text box and see if the resulting email is what you expect.    That would give you a clue about how to format the text for your email.
FYI,  the approach we took was to create an HTML email template with some replaceable strings
e.g. %info_text%
Our preprocessing loading some text from a database,  replaced carriage returns with <br/>,  then used the resulting string to replace %info_text% in our template.
Avatar of RgGray3
RgGray3

I struggled with this a while back...
I ended by going with a 3rd parth solution (FMS emailer)

Alot depends on how your going to use this...
Administrativly (you will manage the process as the IT guy)
or Programatically (set it up for the users to manage)

We ended up using a standalone HTML editor for composition (thus inserting the proper tags within the body text)
then used the FMS emailer to do the sending (which can be configured so a preconfigured job can be sent programatically)

So reviewing your question...  it sounds like your not getting the HTML tags within your body text.

How are you entering the email...  into a text control, a RichText control?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of ianLMurdoch

ASKER

The HTML reference was what I needed thanks to everyone for your help.

Ian