-
Email cration not going to plan
From
noodnutt@21:1/5 to
All on Wed Jul 29 01:34:50 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.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Ron Paii@21:1/5 to
nood...@gmail.com on Wed Jul 29 05:08:18 2020
On Wednesday, July 29, 2020 at 3:34:53 AM UTC-5,
nood...@gmail.com wrote:
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.
You may need to tell Outlook that the body is HTML before setting a value for oItem.HtmlBody
oItem.BodyFormat = OutlookBodyFormatEnum.olFormatHTML ' (2)
I use a enum to make it more readable
Public Enum OutlookBodyFormatEnum
olFormatUnspecified = 0
olFormatPlain = 1
olFormatHTML = 2
olFormatRichText = 3
End Enum
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
noodnutt@21:1/5 to
All on Wed Jul 29 17:56:54 2020
Hi Ron
Thank you for your help.
Although, I incorporated your suggestion, I couldn't understand why it made zero difference, then it hit me ( after a good nights sleep ), the email was already HTML to begin with which was evident in the message title displaying HTML at the end (
something to do with seeing Forests through Trees ).
I did resolve the Line Spacing issue though! Again, a result of clarity from a nights sleep.
I merely needed to add extra "<br></br>" Lines.
Apologies for wasting your time on that issue.
Frustratingly, I still cannot get the signature to show though.
I know the reference line:
strSig = Environ("AppData") & "\Microsoft\mySigs\" & sUser & ".htm"
works because I use the exact same line with zero problems in Excel.
I'll keep searching for a workaround.
Thanks again
Cheers
Mark.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
noodnutt@21:1/5 to
All on Wed Jul 29 22:48:52 2020
Hi Ron
It seems I owe another apology for wasted time.
Again, after a good night sleep things tend to look different.
No idea what made the penny drop, but it did and I sorted out the problem with the signature and I can now finalise this app and give it to it's intended users.
Thanks again for your help.
Cheers
Mark.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
Ron Paii@21:1/5 to
nood...@gmail.com on Thu Jul 30 11:38:47 2020
On Thursday, July 30, 2020 at 12:48:56 AM UTC-5,
nood...@gmail.com wrote:
Hi Ron
It seems I owe another apology for wasted time.
Again, after a good night sleep things tend to look different.
No idea what made the penny drop, but it did and I sorted out the problem with the signature and I can now finalise this app and give it to it's intended users.
Thanks again for your help.
Cheers
Mark.
You should always set the BodyFormat to HTML before filling HTMLBody because you cannot control how the uses setup Outlook.
PS: can you post your solution; others may have the same problem.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)
-
From
noodnutt@21:1/5 to
All on Thu Jul 30 23:44:05 2020
Hi Ron
For anyone who finds themselves in a similar situation.
Here is the final working code:
This code behind the subform.cmdBtn <Send Email>.
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\Subbies Folder\Sub Contractors\Notifications\"
fName = fPath & sFleet & "-" & "Registration" & "-" & 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
Then the following code is in a standard Module:
(I always name my modules so I know where I put a specific code. I named mine mdl_Email_Equipment, you can leave yours default if you prefer).
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)
With oItem
.BodyFormat = 2
End With
oMail.Session.Logon
strSig = Environ("appdata") & "\Microsoft\Signatures\" & 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 & "<br></br>"
myBody = myBody & "<span>" & "Our records show Registration Renewal is approaching" & "</Span>"
myBody = myBody & "<br></br>"
myBody = myBody & "<br></br>"
myBody = myBody & "<span>" & "See the attached file for details" & "</Span>"
myBody = myBody & "<br></br>"
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 & "<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></br>" & mySig
.Attachments.Add fName
.Display
End With
Set oItem = Nothing
Set oMail = Nothing
End Sub
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)