Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 January 10th, 2006, 02:39 AM
Registered User
 
Join Date: Dec 2005
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Send Mail thru Excel Macro

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

 
Old January 10th, 2006, 05:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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 = "name@wherever.com"
        .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
 
Old May 22nd, 2006, 03:58 AM
Registered User
 
Join Date: May 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jeilan_h
Default

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 = "name@wherever.com"
        .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
 
Old May 24th, 2006, 01:12 PM
Registered User
 
Join Date: May 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 21st, 2006, 02:15 AM
Registered User
 
Join Date: May 2006
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jeilan_h
Default

Frends,

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

Thanx.

 
Old July 3rd, 2006, 06:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

 
Old September 21st, 2006, 10:09 AM
Registered User
 
Join Date: Sep 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old September 22nd, 2006, 03:36 AM
Registered User
 
Join Date: Sep 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old November 21st, 2009, 01:05 PM
Registered User
 
Join Date: Nov 2009
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Without Outlook

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
 
Old November 22nd, 2009, 08:23 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

You can try using API Functions

Try googling/bing for MAPISendMail. It should help you

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to send an email with an attachment in it samputha Excel VBA 2 August 13th, 2009 09:12 PM
Excel Macro - Send Email and Attachments robmill101 Excel VBA 1 October 21st, 2008 08:10 AM
Send mail and attachments with PHP mail function Lofa Beginning PHP 1 June 2nd, 2008 03:24 PM
Need macro to send email via default mail program chutchinson Excel VBA 0 September 27th, 2007 09:28 PM
Send It macro in excel hilding Excel VBA 2 September 6th, 2006 05:21 AM





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