Access to send Emails

Posted on 2013-09-03
Medium Priority
Last Modified: 2013-10-07
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 = "info@digbyedwards.com"
        .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

Question by:correlate
  • 6
  • 4
LVL 17

Expert Comment

by:Barry Cunney
ID: 39460888
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

Author Comment

ID: 39460926

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?
LVL 17

Expert Comment

by:Barry Cunney
ID: 39460948
This should out put the details to the debug/output window - From the View menu make sure this window is switched on
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.


Author Comment

ID: 39460985
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"
LVL 17

Accepted Solution

Barry Cunney earned 2000 total points
ID: 39461037
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?

Author Comment

ID: 39461058

Thanks for that, the GetBoiler function code is below, I'm not 100% certain what it does, was taken from this question: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28226876.html

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

LVL 17

Expert Comment

by:Barry Cunney
ID: 39462940
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

Author Comment

ID: 39462957

Thanks for that - have just tested it & it comes out all ok

Author Comment

ID: 39462968
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

Author Closing Comment

ID: 39551506
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

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question