Sending Outlook Emails from Access (1 Viewer)

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Hi guys,

I'm using MS Access 2016. I converted an email (with PDF report attachment) macro to VBA and very happy with the results except for one issue. When Outlook opens the message text always automatically populates on line 2 while the mouse cursor's focus is set to line 1 in the text body (1 line immediately above the auto-populated message text). Any assistance in getting the message text to auto-populate on line 1 would be greatly appreciated.

My vba is:

Code:
Private Sub CmdOpen _Click()
On Error Resume Next
DoCmd.SendObject acReport, "ReportName", "PDFFormat (*.pdf)", _
Me.TextLastname & " "& Me.TextFirstname, "", "", _
"My Subject is", _
"My message text is"
CmdOpenReportname_Click_Exit
End Sub

Kind regards,
Steve
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:08
Joined
Oct 29, 2018
Messages
21,358
Hi Steve. Not sure if the result would be any different but maybe you could try using Outlook Automation in place of the SendObject method. Just a thought...
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Hi DBGuy,

How would I do that?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:08
Joined
Oct 29, 2018
Messages
21,358
Hi DBGuy,

How would I do that?

Hi. Sorry, I’m not in front of a computer right now, so I’ll have to show it to you later, unless someone beats me to it.
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Thanks DBGuy,

LOL....it's 11.05pm here in the land of Oz at the moment. I did find some examples of Outlook Automation from Codekabinett however, I experienced the same issue, where the text body auto-populated on the second line.....not the first line....:banghead:
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:08
Joined
Feb 28, 2001
Messages
26,999
Just as a thought...

Is there a chance that your actual source of subject line ends with a formal line break character? Like vbCrLf (Carriage return + Line feed)?

Are you actually putting in quoted strings or are you using variables for the subject and body elements?
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Hi Doc Man, you're right. I went into text formatting (in Outlook) and discovered an undetectable line break after line 1, but I don't know how to remove it?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:08
Joined
Feb 28, 2001
Messages
26,999
I always used the Outlook App Object to create and send mails, so I didn't have to diddle with the implied "SentObject" method that is essentially a black box. When you use the direct App Object methods, you can get more control.

My first thought is based on my knowledge of the SMTP protocol. Check whether the Subject line ends with a hidden line break because in the order of protocol presentation, the Subject is the immediate predecessor of the Body.
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Hi Doc Man, I can confirm there is a hidden line break on the first row inside the text body of Outlook, but I don't know how to remove it. I'm required to use this method because I work for a government agency that wants us to reduce our practice of saving files to local/shared drives. Instead, they want us to work with documents inside "Approved" container cells and to share documents directly from inside those "Approved" container cells, via email, which I'm hoping this database will become.

Kind regards,
Steve
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:08
Joined
Sep 21, 2011
Messages
14,044
Couldn't you use the Replace function?

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/replace-function

I would expect the character would be CHR(10) or possibly CHR(13) ?
You would have to check?

Hi Doc Man, I can confirm there is a hidden line break on the first row inside the text body of Outlook, but I don't know how to remove it. I'm required to use this method because I work for a government agency that wants us to reduce our practice of saving files to local/shared drives. Instead, they want us to work with documents inside "Approved" container cells and to share documents directly from inside those "Approved" container cells, via email, which I'm hoping this database will become.

Kind regards,
Steve
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Hi Gasman, I was also thinking about that, but I did replace vbCrlf with vbNewLine but no joy. Any other suggestions?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:08
Joined
Sep 21, 2011
Messages
14,044
Hi Gasman, I was also thinking about that, but I did replace vbCrlf with vbNewLine but no joy. Any other suggestions?

I would have thought that would do the same thing?

I was thinking of replacing with nothing, either "" or vbNullString ?
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Hi Gasman, that would be the easy solution. But to become an approved program it needs to contain an approved pre-typed text body....:)
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:08
Joined
Sep 21, 2011
Messages
14,044
Hi Gasman, that would be the easy solution. But to become an approved program it needs to contain an approved pre-typed text body....:)

I am only talking about the very first linefeed?
I am confused now. You say there is an errant character in the body, yet that it is approved pre-type text?

Which is it to be?
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Hi Gasman, it is an approved pre-typed text body that is hard coded (vba). The first line inside Outlook text body should read:

Hi Joe, (example)

However, the salutations appears on the second line inside Outlook's text body. I can also confirm there are 2 spaces and a line break on line 1 inside Outlook's text body.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:08
Joined
Sep 21, 2011
Messages
14,044
Hi Gasman, it is an approved pre-typed text body that is hard coded (vba). The first line inside Outlook text body should read:

Hi Joe, (example)

However, the salutations appears on the second line inside Outlook's text body. I can also confirm there are 2 spaces and a line break on line 1 inside Outlook's text body.

So the text is causing the problem, yet you cannot amend it?
So if you replace the first two spaces with nothing. Then replace the first linefeed with nothing, would that not start the 'pre-typed' text on the first line.?

I'm not sure if you could use " " & Chr(10) as the replace string.

I must admit I am struggling to see the problem.?:banghead:

Edit:
You can use " " & chr(10) in the replace function

Code:
tt="  " & Chr(10) & "This is a test"
? tt
  
This is a test
tt1=replace(tt,"  " & chr(10),"",,1)
? tt1
This is a test
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
So the text is causing the problem, yet you cannot amend it?
So if you replace the first two spaces with nothing. Then replace the first linefeed with nothing, would that not start the 'pre-typed' text on the first line.?

I'm not sure if you could use " " & Chr(10) as the replace string.

I must admit I am struggling to see the problem.?:banghead:

Edit:
You can use " " & chr(10) in the replace function

Code:
tt="  " & Chr(10) & "This is a test"
? tt
  
This is a test
tt1=replace(tt,"  " & chr(10),"",,1)
? tt1
This is a test

My question though, is how would you adapt the above code into this code:
Code:
Private Sub CmdOpen _Click()
On Error Resume Next
DoCmd.SendObject acReport, "ReportName", "PDFFormat (*.pdf)", _
Me.TextLastname & " "& Me.TextFirstname, "", "", _
"My Subject is", _
"My message text is"
CmdOpenReportname_Click_Exit
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:08
Joined
Sep 21, 2011
Messages
14,044
Well I would be not having the errant characters in the text in the first place.:confused:

However if you make the body at least as a variable.
Code:
Private Sub CmdOpen _Click()
Dim strBody as String
On Error Resume Next

strBody = "My message text is"
strBody = Replace(strBody,"  " & Chr(10),"",,1)

DoCmd.SendObject acReport, "ReportName", "PDFFormat (*.pdf)", _
Me.TextLastname & " "& Me.TextFirstname, "", "", _
"My Subject is", _
strBody
CmdOpenReportname_Click_Exit
End Sub
 

Steve G

Registered User.
Local time
Tomorrow, 02:08
Joined
Aug 16, 2019
Messages
24
Hi Gasman, that didn't work unfortunately. Don't worry anymore...I'll just let staff know that they will need to backspace the pre-approved text up to the first line.....Thanks mate, you've been really helpful....:)
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:08
Joined
Sep 21, 2011
Messages
14,044
Hi Gasman, that didn't work unfortunately. Don't worry anymore...I'll just let staff know that they will need to backspace the pre-approved text up to the first line.....Thanks mate, you've been really helpful....:)

I don't think so really:( and stuff like this drives me batty.:banghead:

Good luck with it anyway.;)
 

Users who are viewing this thread

Top Bottom