UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Ms Word Document As The Body Of An Email Created And Sent From Ms Access, Access 2016    
 
   
magicscreen
post Nov 19 2017, 02:39 PM
Post#1



Posts: 432
Joined: 18-November 07



Some of my code is below

The issue I am having is that I am trying to use an unbound object frame as the body of my email.

I am populating the object using OLE with a ms word document which contains text, an image and two hyperlinks.
It populates correctly.

But now I place the object into the MessageBody variable so I can send the email as shown below.

The problem is what type of Variable can I use for the MessageBody?

When I send the email, the text shows but the image and two hyperlinks do not show in the received email.


CODE
Dim MessageTo As String
    Dim Subject As String
    Dim MessageBody As String
    Dim MessageAttachment As String
    
With MailOutLook
                    .BodyFormat = olFormatRichText
                    .To = MessageTo
                    .Subject = Subject
                    .Body = MessageBody
                    
                    If Nz(Me!txtAttachment, "") <> "" Then
                        .Attachments.Add CurrentProject.Path & "\" & txtAttachment & ".rtf"
                    End If
                    
                    .Send
                    
                     intNumberEmailsSent = intNumberEmailsSent + 1
                    '.Display    'Used during testing without sending (Comment out .Send if using this line)
                End With


Thanks.
Go to the top of the page
 
DanielPineault
post Nov 19 2017, 08:24 PM
Post#2


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



First off, using .Body in your Outlook automation limits you to plain text content. If you want to implement more advanced e-mails then you need to use .HTMLBody instead.

As for the direct question of how to use Word doc content as your email... that would be less that ideal IMHO. Word makes an absolute mess of generating HTML. I don't think anything could be worse! If you really want to go down this path, then look over https://forums.slipstick.com/threads/8202-v...-as-email-body/. Here's a slightly adapted version for you to try

CODE
'Purpose: E-mail the specified word document
'Source: https://forums.slipstick.com/threads/8202-VBA-for-using-word-doc-as-email-body/
'Original Author: Sue Mosher
'Adapted by: Daniel Pineault
'Changes:
'           Late Binding
'           Error Handling
'Usage:
'   Call SendDocAsMsg("C:\Users\TestUser\Desktop\test.docx")
Sub SendDocAsMsg(sDoc As String)
    Dim wd                    As Object    'Word.Application
    Dim doc                   As Object    'Word.Document
    Dim itm                   As Object    'Outlook.MailItem
    Dim blnWeOpenedWord       As Boolean
    Const wdDoNotSaveChanges = 0

    On Error Resume Next
    Set wd = GetObject(, "Word.Application")
    If wd Is Nothing Then
        Set wd = CreateObject("Word.Application")
        blnWeOpenedWord = True
    End If
    wd.Visible = False 'True for testing purposes, normally would be = False!

    On Error GoTo Error_Handler
    Set doc = wd.Documents.Open(FileName:=sDoc, ReadOnly:=True)

    Set itm = doc.MailEnvelope.item
    With itm
        .To = "someone@somewhere.com"
        .Subject = "My Subject"
        .Send
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not itm Is Nothing Then Set itm = Nothing
    If Not doc Is Nothing Then
        doc.Close wdDoNotSaveChanges
        Set doc = Nothing
    End If
    If Not wd Is Nothing Then
        If blnWeOpenedWord Then
            wd.Quit
        End If
        Set wd = Nothing
    End If
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: SendDocAsMsg" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
JonSmith
post Nov 20 2017, 04:56 AM
Post#3



Posts: 3,158
Joined: 19-October 10



I agree with DanielPineault, can I challenge why you are using a Word Document?
Perhaps we can think of much better alternatives? I for one have some great stuff with Outlook automation and withevents using .oft files to have email templates that are editable by the user in an easy way.

JS
Go to the top of the page
 
magicscreen
post Nov 20 2017, 07:17 AM
Post#4



Posts: 432
Joined: 18-November 07



DanielPineault and JonSmith,

Thanks for the quick response.

I don't really want to use a Word document.

I have the information currently in a Word document and I thought it might be easier.

I just want to be able to send the email with that information.

The information contains an Image, Text and Two Hyperlinks.

The Hyperlinks create return emails to me.

One is for the recipient to opt out of getting more emails and the other for the user to ask for more information

What I originally tried to do is to copy the information from the word document into some type of field and use that field as the body of the email.

=====================================================================

This is a shortened description of what I want to send.

IMAGE GOES HERE


CONTENT TEXT GOES HERE

Thank you,
Art Sterling, Ameriplan Benefits Consultant

Email: dhpgroup@nj.rr.com

To Unsubscribe: Click Here => This is Hyperlink 1
For more information: Click Here => This is Hyperlink 2


=====================================================================
Go to the top of the page
 
magicscreen
post Nov 20 2017, 07:21 AM
Post#5



Posts: 432
Joined: 18-November 07



JonSmith,

I am not familiar with Outlook automation but am willing to learn.

I think this would be a boon to me.

Any ideas would be greatly appreciated.

Thanks again for your help

magicscreen
Go to the top of the page
 
magicscreen
post Nov 20 2017, 07:35 AM
Post#6



Posts: 432
Joined: 18-November 07



Hi Guys,

I am not sure if this is important but I am sending the same email to many recipients.

I have an Access table with the recipients name, Email Address, the date I sent the Email and the next date to send a second follow up.

I loop through the table and get the email info which is used as the TO: Email box and use the rest of the info to keep me current.

If I get an Opt Out email, I update my table so I don't continue to send emails to that person.

I have to go to work now and should be back around 5:15 PM US Eastern time.

magicscreen
Go to the top of the page
 
JonSmith
post Nov 20 2017, 07:47 AM
Post#7



Posts: 3,158
Joined: 19-October 10



QUOTE
I have the information currently in a Word document and I thought it might be easier.

Easier as opposed to what? (Oh and you are using Outlook automation already, your code in your first post is Outlook Automation!)

So if I understand your needs correctly. You want to dynamically send a mail to many different people based on Access table and you want an email body that is easy to define and change and add hyperlinks and images to it. Using Word was perhaps a way to avoid coding the html?

If I am correct then my solution is ideal for you!! You write the email template in Outlook, that way you get all the rich features associated and can be certain that it looks correct. I used withevents to open the template in a 'template mode' that prevents it from being sent but allows it to be edited (all of this is done from MSAccess).

I'll upload an example later.
Go to the top of the page
 
DanielPineault
post Nov 20 2017, 08:12 AM
Post#8


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



If you want to start managing a mailing list, then I'd urge you to do so through you Web Host. There are a number of mailing programs (some free, some not) that can be easily implemented.

PHPList
MailChimp
VerticalResponse

If you have a WordPress Site, which most people do, then look at https://wordpress.org/plugins/tags/mailing-list/ why reinvent the world.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
magicscreen
post Nov 20 2017, 08:14 AM
Post#9



Posts: 432
Joined: 18-November 07



Guys,

Thank you so much for your help.

I am heading for work now and will look for a response when I return.
Go to the top of the page
 
DanielPineault
post Nov 20 2017, 08:32 AM
Post#10


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



If you still want to stick with Access, then you have a few options:

  • Use Word (code is above)
  • Use Outlook Automation
  • Use CDO Mail automation
  • ...


In also all cases, you will need to generate the raw HTML to be used for the message body. So you need to get familiar with HTML tags, see: https://www.w3schools.com/Tags/default.asp

Here's something to get you going
CODE
Sub f()
    Dim oOutlook              As Object
    Dim oOutlookMsg           As Object
    Dim oOutlookInsp          As Object
    Dim oOutlookRecip         As Object
    Dim oOutlookAttach        As Object
    Dim sTo                   As String
    Dim sSubject              As String
    Dim sBody                 As String
    Const olMailItem = 0

    sTo = "daniel.pineault@cardaconsultants.com"
    sSubject = "E-mail subject line"
    sBody = "<!--fonto:Arial Black--><span style=""font-family:Arial Black""><!--/fonto-->This is a shortened description of what I want to send.<!--fontc--></span><!--/fontc--><br /><br /><!--coloro:#8B0000--><span style=""color:#8B0000""><!--/coloro--><!--sizeo:6--><span style=""font-size:24pt;line-height:100%""><!--/sizeo--> IMAGE GOES HERE  <!--sizec--></span><!--/sizec--><!--colorc--></span><!--/colorc--><br /> <br /><br /><!--sizeo:6--><span style=""font-size:24pt;line-height:100%""><!--/sizeo--> " & vbCrLf & _
            "CONTENT TEXT GOES HERE<!--sizec--></span><!--/sizec--><br /><br /> " & vbCrLf & _
            "Thank you,<br /> " & vbCrLf & _
            "Art Sterling, Ameriplan Benefits Consultant<br /><br /> " & vbCrLf & _
            "Email: dhpgroup@nj.rr.com<br /><br /> " & vbCrLf & _
            "To Unsubscribe: <!--sizeo:6--><span style=""font-size:24pt;line-height:100%""><!--/sizeo--><a href=""http://www....com>CLICK HERE</a><!--sizec--></span><!--/sizec-->=&gt; <!--coloro:#FF0000--><span style=""color:#FF0000""><!--/coloro-->This is Hyperlink 1<!--colorc--></span><!--/colorc--><br /> " & vbCrLf & _
            "For more information: <!--sizeo:6--><span style=""font-size:24pt;line-height:100%""><!--/sizeo--><a href=""http://www....com>CLICK HERE</a><!--sizec--></span><!--/sizec-->=&gt; <!--coloro:#FF0000--><span style=""color:#FF0000""><!--/coloro-->This is Hyperlink 2<!--colorc--></span><!--/colorc--><br /><br /><br />"

    Set oOutlook = CreateObject("Outlook.Application")
    Set oOutlookMsg = oOutlook.CreateItem(olMailItem)

    With oOutlookMsg
        .Display    'Had to move this command here to resolve a bug only existent in Access 2016!
        Set oOutlookRecip = .Recipients.Add(sTo)
        oOutlookRecip.Type = 1

        .Subject = sSubject
        Set oOutlookInsp = .GetInspector    'Retains the signature if applicable
        .HTMLBody = sBody & .HTMLBody
        .Importance = 1    'Importance Level  0=Low,1=Normal,2=High

        For Each oOutlookRecip In .Recipients
            If Not oOutlookRecip.Resolve Then
                oOutlookMsg.Display
            End If
        Next

        .Send 'Use .Display in development for troubleshooting/inspection
    End With

Error_Handler_Exit:
    On Error Resume Next
    If Not oOutlookRecip Is Nothing Then Set oOutlookRecip = Nothing
    If Not oOutlookInsp Is Nothing Then Set oOutlookInsp = Nothing
    If Not oOutlookMsg Is Nothing Then Set oOutlookMsg = Nothing
    If Not oOutlook Is Nothing Then Set oOutlook = Nothing
    Exit Sub

Error_Handler:
    If Err.Number = "287" Then
        MsgBox "You clicked No to the Outlook security warning. " & _
               "Rerun the procedure and click Yes to access e-mail " & _
               "addresses to send your message. For more information, " & _
               "see the document at http://www.microsoft.com/office" & _
               "/previous/outlook/downloads/security.asp."
    Else
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: YourProcedureName" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
    End If
    Resume Error_Handler_Exit
End Sub

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
JonSmith
post Nov 20 2017, 12:04 PM
Post#11



Posts: 3,158
Joined: 19-October 10



QUOTE
In also all cases, you will need to generate the raw HTML to be used for the message body. So you need to get familiar with HTML tags, see: https://www.w3schools.com/Tags/default.asp


That is totally untrue. My method requires the user or coder to know zero html to function. It is all done in the template file which is interacted with as a normal email.
See attached.

The database has two buttons, one to edit the template (which cannot be sent) and one to send the email. It can be tweaked to send mails silently and populate the recipients from an Access table very easily.
Attached File(s)
Attached File  Email_Template.zip ( 50.21K )Number of downloads: 8
 
Go to the top of the page
 
DanielPineault
post Nov 20 2017, 12:30 PM
Post#12


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



Not to be picky, but it is true. The difference is that you use Outlook to generate the HTML for you. This can be a great approach, but never the less, the .HTMBody is receiving HTML content from the Outlook template.

Very nice though.

I have a system which uses a WebBrowser and tinyMCE to allow the user the ability to create such content and store the raw HTML and send that as the .HTMLBody.

In all case, HTML content is required to have such e-mails (colors, links, images, ...) how you generate it is up to you.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
JonSmith
post Nov 20 2017, 12:33 PM
Post#13



Posts: 3,158
Joined: 19-October 10



Ah oke, I read it as you saying you had to generate it as in write it in code or convert it etc etc since you said you need to get familiar with html tags. As you can see that isn't the case, you don't need to be able to read any html to use the method I am recommending. You are correct that html has to be generated somewhere but that method doesn't necessarily require html knowledge.
Go to the top of the page
 
DanielPineault
post Nov 20 2017, 12:35 PM
Post#14


UtterAccess VIP
Posts: 5,451
Joined: 30-June 11



If you haven't already, you should publish your solution to the Code Archive. I'm sure, a solution like yours would help a lot of people.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...
Go to the top of the page
 
JonSmith
post Nov 20 2017, 12:41 PM
Post#15



Posts: 3,158
Joined: 19-October 10



Thanks dude, I didn't get replies on the last couple of Code Archive submissions I made so its been left as pending.....
Go to the top of the page
 
magicscreen
post Nov 20 2017, 06:32 PM
Post#16



Posts: 432
Joined: 18-November 07



JonSmith and DanielPineault,

You guys are great.

I can't thank you enough.

I have some things I have to take care of for work before the holidays so I will look at everything starting on Thanksgiving.

You went beyond just giving answers and I really appreciate it.

If I can get this going, it is going to save me a load of time.

Thanks again,

magicscreen
Go to the top of the page
 
magicscreen
post Nov 23 2017, 11:32 AM
Post#17



Posts: 432
Joined: 18-November 07



JonSmith,

I just downloaded your email app and was able to create and send my email to myself as a test.

I am now going to create the loop of recipients so I can send the email to the group.

The next thing I am going to add is to enter into my database a valueindicating it was sent, the date it was sent and the next date to follow up.

I am using a value to aid in searching.

I haven't done anything with Outlook before I started this project but I am learning a lot.

You have saved me a ton of time and effort and it works beautifully.

Thank you again. You guys are the best.

As an aside in case you ran across this.

I cannot get into File Explorer from the Startup menu, only from the desktop.

There are a few other fields in the startup menu with the same result.

Has anyone come across this issue using Windows 10?

have a great Thanksgiving everyone.

Thanks,

magicscreen.
Go to the top of the page
 
magicscreen
post Nov 24 2017, 04:01 PM
Post#18



Posts: 432
Joined: 18-November 07



JonSmith and DanielPineault,

Again,

I can't thank you guys enough.

I am now able to sent mass emails with hyperlinks, images, etc.

I also update my database with the information of who I sent the emails to as well as when I next have to follow up with them.

You saved me countless hours of research since I haven't done a lot of work with Outlook.

I am closing this case.

magicscreen
Go to the top of the page
 
magicscreen
post Nov 24 2017, 04:01 PM
Post#19



Posts: 432
Joined: 18-November 07



I almost forgot.

Everyone have a great holiday season.

magicscreen
Go to the top of the page
 
magicscreen
post Nov 26 2017, 10:17 AM
Post#20



Posts: 432
Joined: 18-November 07



Hi, its me again.

I don't know if this is possible but each record in my application has, among others, the recipient's name and email address along with a checkbox to CLOSE the record if no further emails are to be sent to that recipient.

When I send emails, some email addresses may not be valid or not in use so if the email cannot be delivered, I get a return email stating that fact.

It would be great if there was a way to get information something into my application telling me that the email could not be delivered due to a bad email address.

If I can get an indicator with the offending email address, my application could search for and mark the specific email as CLOSED so no further emails would be sent to that recipient.

I saw something about a Delivery report but I don't think it applies to my application.

This would make my application much easier and to use save a great deal of time since I now have to go over each email returned due to a bad address and locate the correct record and set the CLOSED checkbox manually.

One issue might be that I am sending the emails in a batch.

I DO NOT need to know if the email was read.

Thanks,

magicscreen
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 10:27 PM