Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 10th, 2006, 01:39 AM
Registered User
 
Join Date: Dec 2005
Location: , , .
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

Reply With Quote
  #2 (permalink)  
Old January 10th, 2006, 04:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 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
Reply With Quote
  #3 (permalink)  
Old May 22nd, 2006, 03:58 AM
Registered User
 
Join Date: May 2006
Location: , , Indonesia.
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
Reply With Quote
  #4 (permalink)  
Old May 24th, 2006, 01:12 PM
Registered User
 
Join Date: May 2006
Location: , , .
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.
Reply With Quote
  #5 (permalink)  
Old June 21st, 2006, 02:15 AM
Registered User
 
Join Date: May 2006
Location: , , Indonesia.
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.

Reply With Quote
  #6 (permalink)  
Old July 3rd, 2006, 06:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 2 Times in 2 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

Reply With Quote
  #7 (permalink)  
Old September 21st, 2006, 10:09 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
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

Reply With Quote
  #8 (permalink)  
Old September 22nd, 2006, 03:36 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
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.

Reply With Quote
  #9 (permalink)  
Old November 21st, 2009, 12: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
Reply With Quote
  #10 (permalink)  
Old November 22nd, 2009, 07:23 AM
Friend of Wrox
Points: 2,950, Level: 22
Points: 2,950, Level: 22 Points: 2,950, Level: 22 Points: 2,950, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 788
Thanks: 1
Thanked 51 Times in 47 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)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 01:13 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.