Email With an Excel Attachment

[chacal]

Hi everyone,

I'm trying to send an email with an Excel attachment. At first, I tried with the code published by Microsoft that use Outlook, but because of the Warning messages that cannot be bypassed, I'd like to find an other way of doing it.

I've eared about the MAPI stuff, but I'm not very familiar with it.

Could someone help me!

Thank you in advance.

 

[DataMiner]

Sending an excel attachment

What warning messages are you referring to?

Here's the way I do it:

'unless outlook is already running, SendObject will ask for a user profile (or something like that)
'so this starts outlook in the background first
SendEmail:
Dim objOutlook As New Outlook.Application
Dim objNameSpace As Outlook.NameSpace
Set objNameSpace = objOutlook.GetNamespace("MAPI")
Call objNameSpace.Logon

'example of how to send strQ as an Excel attachment instead:
DoCmd.SendObject acQuery, strQ, "MicrosoftExcelBiff8(*.xls)", "geezer", "vikcha", "", strTitle, strMsg, False, ""

'now close outlook
objNameSpace.Logoff
Set objNameSpace = Nothing
Set objOutlook = Nothing

 

[chacal]

the first warning that I receive is:

A program is trying to access email addresses you have stored in outlook. Do you want to allow this? (Yes/No)

And the second one ask for a confirmation to send the mail.

Here is the code I used:

Public Sub SendMessage(DisplayMsg As Boolean, Optional File1, Optional File2)

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    ' Create Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

    ' create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
          
    With objOutlookMsg
        ' Adding the receiver
        Set objOutlookRecip = .Recipients.Add("emailAddress")
        objOutlookRecip.Type = olTo
         
        ' Adding Cc
        'Set objOutlookRecip = .Recipients.Add("Michael Suyama")
        'objOutlookRecip.Type = olCC
             
        ' Adding BCc.
        'Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
        'objOutlookRecip.Type = olBCC
        ' Define Object, Body and priority.
        .Subject = "Need Help!!"
        .Body = "I really need help!!." & vbCrLf & vbCrLf
        .Importance = olImportanceHigh  'High

        ' Adding Attachments.
        If Not IsMissing(File1) Then
           Set objOutlookAttach = .Attachments.Add(File1)
        End If
        If Not IsMissing(File2) Then
           Set objOutlookAttach = .Attachments.Add(File2)
        End If
        

        ' Verify the names of the receivers
        For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
        Next

        ' Display the message or not?
        If DisplayMsg Then
            .Display
        Else
            .Save
            .Send
        End If
    End With
    Set objOutlook = Nothing
End Sub

But with this, I receive the errors described above.


I looked at your code, but the problem for me with SendObject is that I cannot attach two Excel files that are already created and stored in a specific place. Or if it allows it, I really don't know how.

 

[DataMiner]

Well, this is probably not the cleanest way, but you could import the excel files into the db (using transferspreadsheet) and then use sendobject as in the code I posted.

 

[DataMiner]

Or, maybe you could even just link to the excel files; then you might be able to use SnedObject and just refer to the excel files as if they were tables in your database. Haven't tried this, but it seems like it might work.

 

[chacal]

all right, I was able to eliminate one of the to warning, and decided to let the ohter one on.

Though, I have an other question:

How can I determine If the message was really sent? In the case that the Internet connection is deactivate? I know that there is a .Sent boolean property that tells if the mail was sent, but right after it execute the .send, the property .sent become empty. I know there is an event that could be caught, but I don't know how to catch it...

 

[pbaldy]

I use a small (free) program called ClickYes to get around the warnings. I've also heard of people using Outlook Redemption. Searching on either should turn up a solution for you.

 

[Newman]

the first warning that I receive is:
A program is trying to access email addresses you have stored in outlook. Do you want to allow this? (Yes/No)

And the second one ask for a confirmation to send the mail.

The customer has a protection against automatic e-mails to prevent worms from spreading. The MAPI you are talking about is a right you give to each customer so that they don't get the message. But since you don't have the rights to give rights in Outlook, and wouldn't want to install it to every one any way, you cannot use the MAPI.

I looked at your code, but the problem for me with SendObject is that I cannot attach two Excel files that are already created and stored in a specific place. Or if it allows it, I really don't know how.

Perhaps you can make these two files become only one. I haven't looked at the database in depth but I think that it might be easier than trying to create code that sends two files.
Anyway, you only have two files to send at once. Couldn't it be sent in two e-mails?

 

[scot22]

Is it possible to insert more than just plain text in the body of the email (such as HTML)?