Send Mail thru Excel Macro

Abhinav · January 10th, 2006, 02:39 AM

Hi,

I want to send a email thru macro, whenever a process is over. Is it possible? Currently I am using Microsoft Outlook 2000 & Excel 2000.

I had gone thur the listed quires & found none to help me out

thanks
Abhinav

maccas · January 10th, 2006, 05:35 AM

Try this. You'll need to add a reference to Outlook in your Visual Basic Project (Tools -> References... -> Microsoft Outlook)

Code:

Sub SendEmail()

Dim OlApp As New Outlook.Application
Dim myNameSp As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myExplorer As Outlook.Explorer
Dim NewMail As Outlook.MailItem
Dim OutOpen As Boolean

    ' Check to see if there's an explorer window open
    ' If not then open up a new one
    OutOpen = True
    Set myExplorer = OlApp.ActiveExplorer
    If TypeName(myExplorer) = "Nothing" Then
        OutOpen = False
        Set myNameSp = OlApp.GetNamespace("MAPI")
        Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox)
        Set myExplorer = myInbox.GetExplorer
    End If
    'myExplorer.Display ' You don't have to show Outlook to use it

    ' Create a new mail message item.
    Set NewMail = OlApp.CreateItem(olMailItem)
    With NewMail
        '.Display ' You don't have to show the e-mail to send it
        .Subject = "Look at this!"
        .To = "[email protected]"
        .Body = "This is a demonstration"
    End With

    'NewMail.Send
    If Not OutOpen Then OlApp.Quit

    'Release memory.
    Set OlApp = Nothing
    Set myNameSp = Nothing
    Set myInbox = Nothing
    Set myExplorer = Nothing
    Set NewMail = Nothing

End Sub

jeilan_h · May 22nd, 2006, 03:58 AM

Hi,

Any idea about the macro to send email with a file as attachment?
for example : "c:\tes\finance.pdf"

Thank you,
Jeilan

Quote:

quote:Originally posted by maccas
Try this. You'll need to add a reference to Outlook in your Visual Basic Project (Tools -> References... -> Microsoft Outlook)

Code:

Sub SendEmail()

Dim OlApp As New Outlook.Application
Dim myNameSp As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myExplorer As Outlook.Explorer
Dim NewMail As Outlook.MailItem
Dim OutOpen As Boolean

    ' Check to see if there's an explorer window open
    ' If not then open up a new one
    OutOpen = True
    Set myExplorer = OlApp.ActiveExplorer
    If TypeName(myExplorer) = "Nothing" Then
        OutOpen = False
        Set myNameSp = OlApp.GetNamespace("MAPI")
        Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox)
        Set myExplorer = myInbox.GetExplorer
    End If
    'myExplorer.Display ' You don't have to show Outlook to use it

    ' Create a new mail message item.
    Set NewMail = OlApp.CreateItem(olMailItem)
    With NewMail
        '.Display ' You don't have to show the e-mail to send it
        .Subject = "Look at this!"
        .To = "[email protected]"
        .Body = "This is a demonstration"
    End With

    'NewMail.Send
    If Not OutOpen Then OlApp.Quit

    'Release memory.
    Set OlApp = Nothing
    Set myNameSp = Nothing
    Set myInbox = Nothing
    Set myExplorer = Nothing
    Set NewMail = Nothing

End Sub

neochichiri · May 24th, 2006, 01:12 PM

Quote:

quote:Originally posted by jeilan_h
Hi,

Any idea about the macro to send email with a file as attachment?
for example : "c:\tes\finance.pdf"

Thank you,
Jeilan

Yes, after .body add the following line:
.Attachments.Add ("C:\tes\finance.pdf")

That should work for you.

jeilan_h · June 21st, 2006, 02:15 AM

Frends,

Seems like the macro just run for Microsoft outlook. Is there any method to send the email through outlook express6 ?

Thanx.

maccas · July 3rd, 2006, 06:17 AM

Jeilan,

You're right - this code is only for Outlook. There is no comparable code for Outlook Express.

Have a look at an earlier post of mine for more info on this issue: http://p2p.wrox.com/topic.asp?TOPIC_ID=40487

Maccas

chuls · September 21st, 2006, 10:09 AM

when i ran this code..i got a pop up from Outlook saying "a program is trying to send a mail. do you wish to continue?"
YES NO HELP

is thr any way we cud avoid this pop up?

thanks in advance

lanewalk · September 22nd, 2006, 03:36 AM

Not easily, and it is really annoying if you are trying to send 100 e-mails. It is a feature of the Outlook Security Update. You can buy software that will switch off the security update whilst you send e-mails but I haven't tried these. There is the option to "Allow access for XX Minutes" which will reduce the number of clicks from 4 to 2 per e-mail which is still a pain.

OldManRiver · November 21st, 2009, 01:05 PM

All,

I need to email a .pdf file from my Excel SS (.pdf is Excel SS gen'd with PrimoPDF), do not use Outlook, never installed and not configured; so how do I get the job done?

OMR

Shasur · November 22nd, 2009, 08:23 AM

Hi

You can try using API Functions

Try googling/bing for MAPISendMail. It should help you

Cheers
Shasur