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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Automating An Email From Access, Office 2010    
 
   
baffled100
post Sep 11 2017, 05:04 PM
Post#1



Posts: 330
Joined: 10-December 12



Hi,
Not sure if this is the best forum for this.......I have a form in Access with a command button to create an email (code is below). I need the email to be in HTML format which I could not get to work with Docmd.SendObject, so I am trying a different way which is new for me. And I'm stuck on a few things......1) there is an email address in the body of the email that I can't seem to get to appear as a hyperlink. I've tried following examples using <a> , but have not had any luck. 2) Is there a way to have the email signature that is set in Outlook for 'new' emails to be added to the automated email? Or is there an easy way to add it using VBA? It has different fonts, colors, sizes, web links. 3) If there isn't a way to get the email signature added programmatically, is it possible to have the program leave the cursor at the end of the email (after 'Regards', preferably two lines below that) so that the user can just insert the signature using the Outlook top menu? Thanks so much!

Private Sub sendemail()
Dim ol As Outlook.Application, msg As Outlook.MailItem
Dim eBody As String
Dim toEmail As String
Dim thsubject As String
Dim stfirstname As String
Dim stresperiod As Variant

On Error Resume Next
Set ol = CreateObject("Outlook.Application")
Set msg = ol.CreateItem(olMailItem)
stfirstname = Me![NAME_FIRST]
stresperiod = Forms![applications menu]!periodname & " " & Left(Forms![applications menu]!currapp, 4)

eBody = "Dear " & stfirstname & "," & "<br>" & "<br>" _
& "Thank you for your application to XYZ " & stresperiod & " residency period (February 1, 2018 - May 31, 2018). We have received all required application materials and your application is being processed." & "<br>" & "<br>" _
& "We will be sending you the results of your application by email no later than November 15, 2017. To ensure you receive our notification, please add jjones@xyzco.org to your email address book/safe sender's list." & "<br>" & "<br>" _
& "Please let us know if you have questions." & "<br>" & "<br>" _
& "Regards," & "<br>" & "<br>"
With msg
.To = Me![EMAIL] 'toEmail
.Subject = "Notification of application receipt from XYZ" 'theSubject
.HTMLBody = eBody 'theBody
.Display 'Send
End With
If Not msg Is Nothing Then Set msg = Nothing
If Not ol Is Nothing Then Set ol = Nothing
End Sub
Go to the top of the page
 
DanielPineault
post Sep 11 2017, 05:23 PM
Post#2


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



Your best bet is to use a reusable function, such as: https://www.devhut.net/2010/09/03/VBA-send-...ook-automation/ and then simply call it whenever you need to send an e-mail by doing something like

CODE
Private Sub sendemail()
    On Error GoTo Error_Handler
    Dim sBody                 As String
    Dim sTo                   As String
    Dim sSubject              As String
    Dim sFirstName            As String
    Dim sResPeriod            As Variant

    sFirstName = Me![NAME_FIRST]
    sResPeriod = Forms![applications menu]!periodname & " " & Left(Forms![applications menu]!currapp, 4)
    sBody = "Dear " & sFirstName & "," & "<br>" & "<br>" _
            & "Thank you for your application to XYZ " & sResPeriod & " residency period (February 1, 2018 " & _
            "- May 31, 2018). We have received all required application materials and your application is " & _
            "being processed." & "<br>" & "<br>" _
            & "We will be sending you the results of your application by email no later than November 15, " & _
            "2017. To ensure you receive our notification, please add " & _
            "<a href=""mailto:jjones@xyzco.org"">jjones@xyzco.org</a> to your email address " & _
            "book/safe sender's list." & "<br>" & "<br>" _
            & "Please let us know if you have questions." & "<br>" & "<br>" _
            & "Regards," & "<br>" & "<br>"
    sTo = Me![EMAIL]
    sSubject = "Notification of application receipt from XYZ"

    Call SendHTMLEmail(sTo, sSubject, sBody, True)

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: sendemail" & 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
 
ADezii
post Sep 11 2017, 06:24 PM
Post#3



Posts: 1,780
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
Is there a way to have the email signature that is set in Outlook for 'new' emails to be added to the automated email? Or is there an easy way to add it using VBA?

It is not exactly intuitive how you would automatically add an Outlook Signature to the end of an E-Mail using VBA, but here it goes:
  1. The Signature Files exist in a specific location on your Hard Drive for each Signature, and are located at:
    CODE
    Environ("appdata") & "\Microsoft\Signatures\" & "SignatureName.txt/.htm/rtf"
  2. The signature exists in three Formats with the same Base Name: .txt, .htm, and .rtf. Experiment with each.
  3. The following Code Segment is AIRCODE, since I currently have no way to test it.
  4. For this Demo, the Base Signature Name is MySignature and exists in the previously stated forms, in the pre-defined Folder.
    CODE
    MySignature.txt
    MySignature.htm
    MySignature.rtf
  5. Try calling this Function at the end of your Body String, appending it's Return Value.
    CODE
    Public Function fReturnSig() As String
    Dim strLine As String
    Dim strBuild As String
    Dim strSignature As String

    strSignature = "MySignature.htm"
    'strSignature = "MySignature.rtf"
    'strSignature = "MySignature.txt"

    Open Environ("appdata") & "\Microsoft\Signatures\" & strSignature For Input As #1

    Do While Not EOF(1)
      Line Input #1, strLine
        strBuild = strBuild & strLine & vbCrLf
    Loop

    Close #1

    fReturnSig = Left$(strBuild, Len(strBuild) - 2)
    End Function
  6. Good Luck with your Project.
Go to the top of the page
 
DanielPineault
post Sep 11 2017, 06:52 PM
Post#4


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



For the issue with the signature, actually, with Outlook automation, you need only use one line

CODE
Set oOutlookInsp = .GetInspector


to get the default signature to be included. This is already part of the function I provided a link to.

--------------------
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
 
baffled100
post Sep 12 2017, 09:59 AM
Post#5



Posts: 330
Joined: 10-December 12



Wow! Thank you both so much for this help! I will give it a try a bit later when I am back in the office and report back! Thanks again!
Go to the top of the page
 
baffled100
post Sep 12 2017, 02:00 PM
Post#6



Posts: 330
Joined: 10-December 12



Thanks again to you both! I was not able to get the signature to work with Daniel's code, but I was able to get it work with ADezii's code.....almost perfectly.....we have the Facebook, Twitter and Instagram image links in our email signature, and the links are there (seen if you hover over the blank box) in the signature in the email , but not the images. It's not a big problem to have a separate signature for this purpose that does not have those images, but just in case it is an easy fix, I thought I'd mention it!

Thank you!!
This post has been edited by baffled100: Sep 12 2017, 02:00 PM
Go to the top of the page
 
DanielPineault
post Sep 12 2017, 02:11 PM
Post#7


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



iconfused.gif I've used the code for years, Access 2007+, in multiple companies and it always works?! Very odd!

--------------------
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
 
baffled100
post Sep 12 2017, 02:36 PM
Post#8



Posts: 330
Joined: 10-December 12



Oh, I have no doubt that it works, and I know it is something that I am not doing correctly smile.gif

I did just notice something odd though. The font of the salutation line (Dear first name) is different from the font of the rest of the email. If sbody includes both the salutation and the body, why would the fonts be different? I looked at my default Outlook settings and the salutation line is using the default font set for 'New mail messages' and the body is using the default font set for 'Composing and reading plain text messages'......I could set them to be the same, but it doesn't make sense to me if the whole body of the email is one expression/variable.....

Thanks again!
Go to the top of the page
 
baffled100
post Sep 12 2017, 02:50 PM
Post#9



Posts: 330
Joined: 10-December 12



Just sort of figured out what is going on with the fonts.....the 'New mail messages' default is set to +Body, which is not something I was aware of or have yet to figure out......always something new to learn!
Go to the top of the page
 
baffled100
post Sep 12 2017, 04:12 PM
Post#10



Posts: 330
Joined: 10-December 12



This +Body feature is confusing me......I simply want the email to be ALL the same font! Can that be set in the program to override Outlook settings? Thank you!
Go to the top of the page
 
baffled100
post Sep 12 2017, 08:07 PM
Post#11



Posts: 330
Joined: 10-December 12



As a workaround, I added a blank line to the beginning of the email (<br>)......and that worked--now the whole email is in the same font......but I have NO idea why the first line of the email --which was 'Dear first name'--was a different font! Where there's a will, there's a way! smile.gif
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th September 2017 - 05:28 PM