L
Lenny Wintfeld
Hi,
I have the following problem sending long strings in the body of an
email (using Outlook) from an Excel vba program. Here are the details:
I've got a vba program in Excel that obtains data from a variety of
locations in several spreadsheets. The program creates comma delimited,
CRLF terminated records, and emails the records in the body of an email.
Emailing is handled by late binding to Outlook.
Each record ranges from about 90 to 140 characters long. The complete
set of records (the body of the email) is 4-6K bytes. The records each
end in <CRLF> (e.g. &H0D, &H0A). I'm certain that the records are well
formed (I also write the string comprising the body of the email to a
local file that I open (with VBA)in binary mode. I verfy the file by
opening it in both binary and text mode using the MS Visual Studio
editor, and it looks perfect).
The problem is that Outlook (or the Autoamtion interface to Outlook)
insists on modifying the body of the email, by adding extra CRLF's
and/or wrapping lines. I also find that the recipient client (also
Outlook) likes to somtimes help out by deleting some CRLF's to "help me
out"!!
I have the following constraint imposed on me by my client: The data
must be whole records in the body of an email.
My main question is: Is there a way to send these rather long lines
using late binding to the Outlook Automation interface without Outlook
(or its interface) modifying what I give it? If not I'll have to go
seach for a 3rd party VBA email library to use instead of Outlook; but
it seems to me that Outlook should be able to do this (as I read it, RFC
2821 says lines can be 1000 characters long and email bodies can be 64
K).
Here is the code on the Excel side that interfaces to Outlook. "Body" is
a string containing all the records with CRLF's inserted bytewise by
another VBA Sub. "CCRecipient" is not used in the code any more, because
I have not been able to get late binding to the Outlook Autoamtion
object to accept a CC recipient. The function always returns True, for
now.
Public Function SendEMail(Recipient As String, CCRecipient As String,
Subj As String, Body As String) As Boolean
Dim App As Object
Dim Mail As Object
Set App = CreateObject("Outlook.Application")
Set Mail = App.CreateItem(0)
With Mail
.Subject = Subj
.Recipients.Add (Recipient)
.BodyFormat = 1 'olFormatPlain
.Body = Body
End With
Mail.Send
Set Mail = Nothing
Set App = Nothing
SendEMail = True
End Function
Any help getting Outlook to just take what I give it and send it as
given will be greatly appreciated.
Extra points to anyone who can also help me figure out why I can't enter
a CC recipient
(That code is no longer present in the above
function, but it consisted of doing another .Recipeints.Add and
following that with setting the recipient type to the numerical value of
the olTypeCC manifest constant.)
Thanks in advance for any help.
Lenny Wintfeld
(long time C/C++ programmer, currently figuring out what VBA is about)
I have the following problem sending long strings in the body of an
email (using Outlook) from an Excel vba program. Here are the details:
I've got a vba program in Excel that obtains data from a variety of
locations in several spreadsheets. The program creates comma delimited,
CRLF terminated records, and emails the records in the body of an email.
Emailing is handled by late binding to Outlook.
Each record ranges from about 90 to 140 characters long. The complete
set of records (the body of the email) is 4-6K bytes. The records each
end in <CRLF> (e.g. &H0D, &H0A). I'm certain that the records are well
formed (I also write the string comprising the body of the email to a
local file that I open (with VBA)in binary mode. I verfy the file by
opening it in both binary and text mode using the MS Visual Studio
editor, and it looks perfect).
The problem is that Outlook (or the Autoamtion interface to Outlook)
insists on modifying the body of the email, by adding extra CRLF's
and/or wrapping lines. I also find that the recipient client (also
Outlook) likes to somtimes help out by deleting some CRLF's to "help me
out"!!
I have the following constraint imposed on me by my client: The data
must be whole records in the body of an email.
My main question is: Is there a way to send these rather long lines
using late binding to the Outlook Automation interface without Outlook
(or its interface) modifying what I give it? If not I'll have to go
seach for a 3rd party VBA email library to use instead of Outlook; but
it seems to me that Outlook should be able to do this (as I read it, RFC
2821 says lines can be 1000 characters long and email bodies can be 64
K).
Here is the code on the Excel side that interfaces to Outlook. "Body" is
a string containing all the records with CRLF's inserted bytewise by
another VBA Sub. "CCRecipient" is not used in the code any more, because
I have not been able to get late binding to the Outlook Autoamtion
object to accept a CC recipient. The function always returns True, for
now.
Public Function SendEMail(Recipient As String, CCRecipient As String,
Subj As String, Body As String) As Boolean
Dim App As Object
Dim Mail As Object
Set App = CreateObject("Outlook.Application")
Set Mail = App.CreateItem(0)
With Mail
.Subject = Subj
.Recipients.Add (Recipient)
.BodyFormat = 1 'olFormatPlain
.Body = Body
End With
Mail.Send
Set Mail = Nothing
Set App = Nothing
SendEMail = True
End Function
Any help getting Outlook to just take what I give it and send it as
given will be greatly appreciated.
Extra points to anyone who can also help me figure out why I can't enter
a CC recipient
(That code is no longer present in the abovefunction, but it consisted of doing another .Recipeints.Add and
following that with setting the recipient type to the numerical value of
the olTypeCC manifest constant.)
Thanks in advance for any help.
Lenny Wintfeld
(long time C/C++ programmer, currently figuring out what VBA is about)