Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2002
    Posts
    10

    Cool Unanswered: Automation problem

    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
    Exit For
    End If
    Next i
    Cont = Left(Econt, sCount - 1)
    sCount = 0
    With objOutlookMsg

    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
    objOutlookRecip.Resolve
    Next
    If DisplayMsg Then
    .Display
    Else
    .Send
    End If
    End With
    Err_Send_Message:
    If Err.Number = conStopError Then
    MsgBox "You Have Cancelled The Message"

    End If
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    Set objOutlookRecip = Nothing
    Set objOutlookAttach = Nothing



    End Sub

    as it is now the code does not attach a document but prints the body text within the email

    I have checked and rechecked the coding any ideas
    Terry

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think you need to declare that attachment object... Give it a shot with just:

    If Not IsMissing("C:\My Documents\Terry.Doc") Then
    .Attachments.Add("C:\Documents and Settings\Terry Myers\My Documents\Terry.Doc")
    End If
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2002
    Posts
    10

    Cool Still does not work

    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.

    Any more ideas would be appreciated

    Thanks in advance

    Terry.
    Terry

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I use the following (and it works):
    Code:
        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.Attachments.Add (strFileAttachment)
        itmMess.Subject = strMessageSubject
        itmMess.To = strTOemail
        itmMess.BCC = strBCCemail
        itmMess.Send
        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.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Note:

    I used the code shown above in the following combinations:
    - Access 97 with Outlook 2000
    - Access 97 Run-Time with Outlook 2003
    - Access 2000 with Outlook 2003
    - Access 2003 with Outlook 2003

    It will NOT work with Outlook versions older than 2000.
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  6. #6
    Join Date
    Feb 2002
    Posts
    10

    Question Still does not work

    Hi Todd.

    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.

    Terry.
    Terry

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I tried it on a laptop and it works okay
    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.

    tc

  8. #8
    Join Date
    Feb 2002
    Posts
    10

    Cool Further clue to fault

    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.

    Any ideas would be appreciated.
    Terry

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Terry,

    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.

    tc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •