Access to send Emails

Dear Experts

I am building a mailshot database  using Access - this will allow users to send a personalised mailshot (Dear John, Dear Paul etc.) with an attachment & with an RTF signature block (really keen not to use am HTML one as the pictures never seem to come out)

I haven't started on the personalisation piece (which will be a simple rst) but have managed to add the signature block & attachment to the email.  However I cannot add the content of the email (which comes from a Rich Text formatted field [email_body].  

I think the adding of the RTF signature block somehow clashes with the formatting of [email_body] so that bit doesn't get added.

Below is the code I am using - can anyone help?

Private Sub SendMail_Click()

    Dim OutApp As Object
    Dim OutMail As Object
' create content Strings
    Dim BodyString As String
    Dim DefaultSignatureString As String
    Dim Signature As String
    Dim AttachmentString As String
    Dim SubjectLine As String
    SubjectLine = Me.Subject_Line
    AttachmentString = Me.Attachment
    BodyString = Me.Email_Body
    DefaultSignatureString = Me.Email_Signature
        If Dir(DefaultSignatureString) <> "" Then
        Signature = GetBoiler(DefaultSignatureString)
        Signature = ""
    End If

'Create Outlook Session

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next

    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = SubjectLine
        .RTFBody = BodyString & "<br>" & Signature
        .Attachments.Add AttachmentString
        .Send    'or use .Display
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
Please do a Debug.Print of BodyString & "<br>" & Signature and post it up so as we can see the content of this - it may be a case that there is a missing closing tag or something like that
correlateAuthor Commented:

I've put in the code Debug.Print BodyString & "<br>" & Signature , but sadly I have no idea how to see what the result of that is - what do I do?
Barry CunneyCommented:
This should out put the details to the debug/output window - From the View menu make sure this window is switched on
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

correlateAuthor Commented:
here we go ...

  : BodyString : "<div>Hello</div>


<div><font color=red>Hello in red</font></div>" : String

  : DefaultSignatureString : "C:\Users\tcrowfoot\AppData\Roaming\Microsoft\Signatures\New Logo.rtf" : String

  : Signature : "{\rtf1\adeflang1025\ansi\ansicpg1252\uc1\adeff31507\deff0\stshfdbch31505\stshfloch31506\stshfhich31506\stshfbi31507\deflang2057\deflangfe2057\themelang2057\themelangfe0\themelangcs0"
Barry CunneyCommented:
The rtf signature does not appear to have a closing }

It seems to be the call to  Signature = GetBoiler(DefaultSignatureString) that processes the signature.
What does the GetBoiler function do?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
correlateAuthor Commented:

Thanks for that, the GetBoiler function code is below, I'm not 100% certain what it does, was taken from this question:

Function GetBoiler(ByVal sFile As String) As String
'Dick Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
End Function

Open in new window

Barry CunneyCommented:
As a test, please try excluding the signature and confirm if the e-mail body gets created and formatted OK

Change the line

.RTFBody = BodyString & "<br>" & Signature


.RTFBody = BodyString
correlateAuthor Commented:

Thanks for that - have just tested it & it comes out all ok
correlateAuthor Commented:
Argh, actually it doesn't (I had it sending as .HTMLbody as I was trying a few other solutions)

When sending just the body of the email as an RTF the attachment is in the main body of the email rather than in the attachments
view of email
correlateAuthor Commented:
Thanks for your help on this one - the closing "}" was the offending item - there didn't seem to be a way of adding that in in a consistent manner across all user's signature blocks, so have adapted the Db so users copy & paste their signature block into the main body of the email
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.