Email cration not going to plan

Giganews Newsgroups
Subject: Email cration not going to plan
Posted by:  noodnutt (noodnu…@gmail.com)
Date: Wed, 29 Jul 2020

Hi Team

Due to the length og this post, let me start out by thanking you for your time.

The actual process to create the email, with the .pdf attachment works as expected, the annoying part is the body is not doing what I would like it to do. I know I shouldn't assume just because something works in Excel, it should work in Access.

Using Office 2013 / Access v15.0 over a network.

Three issues prevail, but I can only try to remedy two because the third is the Outlook Security Warning, which my I.T. Dept will flatly refuse to allow me access to change the settings so as not to appear.

1. Signature not showing.

I use the GetBoiler() Function in Excel with zero issues, and have seen it
appear in many Access posts I have read, but for some reason, I can't get it to work in my Project.

I get the sUser.Name ( which is dropped into an Unbound Control on the Parent.From "frmDashboard" ), it is visible so I know it's there.

And!

2. No Line Breaks Inserting into HTMLBody.myBody

Again, I use the below myBody string in many Excel Apps which lays out the strings without issue, but! once again, in Access it doesn't work the same for some reason.

Here are the codes that almost get me over the line:

This code resides behind a Subform: "sfrm_30_E", the Parent.Form is "frmDashboard".

Private Sub cmdBtn_Email_E_Click()

    Dim rName As String, fPath As String, fName As String
    Dim rArg As String, fDate As String
    Dim sFleet As String, fEmail As String
    Dim sUser As String

    Me.txfFleetNo = Me.txfConFleetNo
    Me.txfFleetNo.Requery

    sUser = Me.Parent!txfWhoAmI
    sFleet = Me.txfFleetNo
    fDate = Format(Now(), "yyyymmdd")
    fEmail = Me.txfConEmail
    rName = "rptEmail_E30"
    fPath = "T:\Compliance\Subbie Folder\Sub Contractors\Notifications\"
    fName = fPath & sFleet & "-" & fDate & ".pdf"
    rArg = "lnfConRecNo = " & lnfConRecNo & ""

    DoCmd.OpenReport rName, acViewPreview, , rArg, acHidden
    DoCmd.OutputTo acOutputReport, rName, acFormatPDF, fName
    DoCmd.Close acReport, rName, acSaveNo

    Call Mdl_Email_Equipment.Email_E(sUser, rName, fPath, fName, sFleet, rArg, fEmail)

End Sub

Public Sub Email_E(sUser As String, rName As String, fPath As String, fName As String, sFleet As String, rArg As String, fEmail As String)

    Dim oMail As Outlook.Application
    Dim oItem As Outlook.MailItem
    Dim strSig As String, mySig As String
    Dim myBody As String

    Set oMail = CreateObject("Outlook.Application")
    Set oItem = oMail.CreateItem(0)

    oMail.Session.Logon

    strSig = Environ("AppData") & "\Microsoft\mySigs\" & sUser & ".htm"

    If Dir(strSig) <> "" Then
        mySig = GetBoiler(strSig)
    Else
        mySig = ""
    End If

    myBody = "<html><Body><span><font size=12pt>" & "Hi" & "</span>"
    myBody = myBody & "<br></br>"
    myBody = myBody & "<span>" & "Our records show Registration Renewal is approaching" & "</Span>"
    myBody = myBody & "<br></br>"
    myBody = myBody & "<span>" & "See the attached file for details" & "</Span>"
    myBody = myBody & "<br></br>"
    myBody = myBody & "<span>" & "If you have replaced this Equipment, please let us know immediately" & "</Span>"
    myBody = myBody & "<br></br>"
    myBody = myBody & "<span>" & "Regards" & "</span>"
    myBody = myBody & "<br></br></font></Body></html>"
    On Error Resume Next

    With oItem
        .To = fEmail
        .CC = ""
        .Subject = "Equipment Registration Renewal"
        .Importance = olImportanceHigh
        .HTMLBody = myBody & "<br>" & mySig
        .Attachments.Add fName
        .Display
    End With

    Set oItem = Nothing
    Set oMail = Nothing

End Sub

Cheers
Mark.

Replies