Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 21st, 2005, 04:52 AM
Authorized User
 
Join Date: Oct 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error Message

Hello,

Can someone help me with the below. I am having trouble with the below code for which I get the following error message. I have added the CDO reference, which I thought may be the problem, but if anyone can help, it would be most benefical

Collaboration - Data Objects Mapi_e_not_found 8004010f)]]
Run Time Error 2147221233

Public Sub SendEmail()

Dim i As Integer
Dim strSubject As String
Dim strMessage As String
Dim strRecipients As String
Dim sh As Worksheet
Dim wbSend As Workbook

Dim objSession As MAPI.Session
Dim objNewMessage As MAPI.Message
Dim objRecipient As MAPI.Recipient
Dim objAttachment As MAPI.Attachment

    ' Check the user is ok to send
    If MsgBox(Prompt:="Ok?", Buttons:=vbYesNo) = vbNo Then Exit Sub

    ' Set the subject of the e-mail
    strSubject = ""

    ' Set the text of the e-mail
    strMessage = ""

    ' Set the recipient names
    strRecipients = "me@anywhere.com;you@overthere.com"

    ' Save a tempory copy of the sheet with the correct filename
    Set sh = ThisWorkbook.Sheets("Details")
    Set wbSend = Workbooks.Add

    sh.Copy Before:=wbSend.Sheets(1)

    FName = "C:\..."
    wbSend.SaveAs Filename:=FName

    ' Close the Temp file
    wbSend.Close SaveChanges:=False

    ' Start CDO session
    Set objSession = New MAPI.Session
    objSession.Logon "", "", False, False

    ' Create a new message
    Set objNewMessage = objSession.Outbox.Messages.Add
    With objNewMessage

        .Subject = strSubject
        .Text = strMessage

        ' Add recipients one by one and resolve against the directory
        i = InStr(1, strRecipients, ";", vbBinaryCompare)
        Do Until i = 0
            Set objRecipient = .Recipients.Add
            objRecipient.Name = Left(strRecipients, i - 1)
            objRecipient.Resolve
            strRecipients = Mid(strRecipients, i + 2)
            i = InStr(1, strRecipients, ";", vbBinaryCompare)
        Loop
        Set objRecipient = objNewMessage.Recipients.Add
        objRecipient.Name = strRecipients
        objRecipient.Resolve

    End With

    ' Attach the tempory file to the e-mail
    Set objAttachment = objNewMessage.Attachments.Add

    objAttachment.Position = 0

    objAttachment.Type = CdoFileData
    objAttachment.ReadFromFile FName
    objAttachment.Source = FName

    ' Delete the tempory copy
    Kill FName

    ' Send the e-mail
    objNewMessage.Update
    objNewMessage.Send

    'Release memory
    Set objNewMessage = Nothing
    Set objSession = Nothing
    Set objAttachments = Nothing
    Set objRecipient = Nothing

    ' Reassuring message
    MsgBox "File send successful"

End Sub
 
Old November 21st, 2005, 01:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Alan,

It would help immensely if you could tell us which line the code is crashing out on. Basically CDO wraps up MAPI code libraries and presents them in slightly nicer format for VB to access. The error you are getting there is a pretty generic error being passed back up to CDO by MAPI which is saying "I don't like that". As such, the context of exactly which line is crashing out is extremely helpful.

If, as I suspect, it is crashing out on the line
Code:
objSession.Logon "", "", False, False
then what is happening is that your logon profile doesn't have defualt access rights to your exchange server. I'm not sure how you would go about fixing this save for passing Username and password details (as well as maybe the exchange server etc.)

I'm actullay a bit (/lot!) out of my depth here in figuring out what's going wrong for you but will have a dig around if you post back on you error. I can recommed that you try posting on Google groups, which has a MASSIVE readship of very learned people or have a look at http://www.cdolive.com/.

Cheers,
Maccas
 
Old November 22nd, 2005, 04:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

I've just had a look around and the following MS kb article should help you out if you're struggling with logon: http://support.microsoft.com/?kbid=171422

It seems the code I gave you only allows you to logon to an existing session (i.e. assumes you're running Outlook concurrently).
 
Old November 25th, 2005, 05:45 AM
Authorized User
 
Join Date: Oct 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The point at which this is failing is here:

   objAttachment.ReadFromFile FName

What I am meant to do?

Thanks

Alan
 
Old November 25th, 2005, 11:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Alan,

I'm not sure I'm going to be able to help you here as I don't fully understand why you are crashing out. You could try the more condensed attachment add method:
Code:
Set objAttachment = objNewMessage.Attachments.Add _
    Name:="Display Name", Position:=0, _
    Type:=CdoFileData, Source:= FName
as opposed to the current code you've got of:
Code:
    Set objAttachment = objNewMessage.Attachments.Add

    objAttachment.Position = 0

    objAttachment.Type = CdoFileData
    objAttachment.ReadFromFile FName
    objAttachment.Source = FName
Like I said earlier you might get some joy by posting the saem problem on Google groups.

Maccas





Similar Threads
Thread Thread Starter Forum Replies Last Post
What is the error message for a 500 server error? chobo2 C# 2005 1 May 4th, 2008 03:11 AM
ERROR MESSAGE underbuchha Visual C++ 2005 0 September 30th, 2006 02:50 AM
Where does the error message go? Wei Wang BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 1 March 13th, 2006 02:03 PM
error message. Tasha Access VBA 2 August 11th, 2004 11:07 PM
Help with Error message mariakovacs Classic ASP Databases 3 October 10th, 2003 03:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.