I have a problem that when trying to email from Access using automation I can hardcode the body text of the email, but when the code comes to attach a word document the attachment is is not shown in outlook but when I comment the lines that deal with the body of the text the attachment is then shown. If I comment the lines that deal with the attachment then the body text shows in the email. I am using office 2003, Access 2003, Outlook2003 with the office sp2. The code used to work but I had not used the database for a while. and have only just noticed this anomally. It does not produce any error codes so can only assume that the code works but something is preventing it working properly. The code is shown below.
Public Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
On Error GoTo Err_Send_Message
Const conStopError = 287
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim Emctl As Control
Dim Eref As Control
Dim Econt As Control
Dim fStop As String
Dim sCount As Integer
Dim Cont As String
Dim CharsInString As Integer
Dim i As Integer
Dim j As Integer
Dim Char As String
Dim IntNum As Integer
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Set Emctl = Forms![Job]![email]
Set Eref = Forms![Job]![Ref]
Set Econt = Forms![Job]![Contact]
fStop = Chr$(46)
sCount = 0
CharsInString = Len(Econt)
For i = 1 To CharsInString
Char = Mid(Econt, i, 1)
IntNum = Asc(Char)
sCount = sCount + 1
If IntNum = 32 Then
Cont = Left(Econt, sCount - 1)
sCount = 0
Set objOutlookRecip = .Recipients.Add(Emctl)
objOutlookRecip.Type = olTo
.Subject = "Job Position"
.Body = "Ref : " & Eref & vbCrLf & vbCrLf & "Dear " & Cont & fStop & vbCrLf & vbCrLf & "I am now actively seeking a new position, preferably permanent. I would like to take this opportunity to send you my C.V." & vbCrLf & vbCrLf & "Awaiting your reply." & vbCrLf & vbCrLf & "Yours Sincerely" & vbCrLf & vbCrLf & "Terry Myers" & vbCrLf & vbCrLf
' If Not IsMissing("C:\My Documents\Terry.Doc") Then
' Set objOutlookAttach = .Attachments.Add("C:\Documents and Settings\Terry Myers\My Documents\Terry.Doc")
' End If
For Each objOutlookRecip In .Recipients
If DisplayMsg Then
If Err.Number = conStopError Then
MsgBox "You Have Cancelled The Message"
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing
as it is now the code does not attach a document but prints the body text within the email
Have tried the lines in the code, it does not work when you run it after uncommenting the block, the attachment is shown in the finished email. but the body text is not shown. If you comment the block of code which deals with the attachment then the body text is shown. In order to achieve what I need, both the body text and the attachment need to be showing in the email.
The problem is quite perplexing as it works on my laptop.
Dim olApp As Outlook.Application
Dim nspNameSpace As Outlook.NameSpace
Dim itmItems As Outlook.Items
' The Strings come in as parameters of the function
Set olApp = New Outlook.Application ' Application object.
Set nspNameSpace = olApp.GetNamespace("MAPI") ' Namespace object.
Set itmMess = olApp.CreateItem(olMailItem) ' New Message
itmMess.Body = strMessageBody
itmMess.Subject = strMessageSubject
itmMess.To = strTOemail
itmMess.BCC = strBCCemail
Set itmMess = Nothing
Set nspNameSpace = Nothing
Set olApp = Nothing
The only significant difference I see between our methods is that I am doing everything using actions, properties and collections of the "Message Object" directly.
PS: This code brought to you courtesy of various dbforums contributors and maybe one or two lines from Microsoft.
Thanks for the suggestion, it still doe's not work. The only thing I can think of is that there is something on the machine which is blocking the program from working, I tried it on a laptop and it works okay. Like I said if comment the block of code containing the body text then the attachment is shown. If I comment out the code relating to the attachment then the body text is shown. Im beginning to think it may be a problem with the service packs ive installed on the machine. I.E a security issue but then it shows the dialog box relating to allowing access to outlook so it should work. Any more suggestions would be appreciated.
ok - you've identified the problem. If the code was bad, it wouldn't work in either environment.
What ever is different detween your laptop and your PC is leading to the problem. It may be as simple as the settings of Outlook. I've noticed that sometimes Outlook shows attachments in line with the message body as an icon and other times it shows them as a seperate field on the message. I never looked into what drives the differences.
I have found that if I open outlook first, and run minimised on the task bar then run the coding it works as it should. Both the text and the attachment are shown in the email. I have checked the laptop for any differences but nothing is obvious.
It hasn't mattered whether Outlook was open or not with my application of the code shown.
One thing to note: when I run my code, I never get to see the email message on my screen - it all happens "behind the scenes". I know that it works by checking the sent mail folder and by including my own email in the list of addresses.