Excel sending an email

I need to be able to send an email via excel using the contents of a worksheet to populate the email. Then I need to be able to select the outlook mailbox which I wish to send the email from which won't be the default for outlook.
Sam CoombesAsked:
Who is Participating?
 
yo_beeConnect With a Mentor Director of Information TechnologyCommented:
Here is a module that I use in my weekly reports that need to be e-mailed to all upper management.

Sub Email_Report(ByVal Fname As String)
    'sourced from:Automation of out look Mails based on the excel list
    'http://www.thecodecage.com/forumz/1054996151-post10.html
    'http://www.thecodecage.com/forumz/microsoft-outlook-forum/213688-search-new-email-text-make-bold-blue.html#post1055005238
    'RB: other constants to allow Late Binding*
    'Const olFolderInBox As Long = 6
    Dim OutApp As Object
    Dim oNameSpace As Object
    Dim OutMail As Object

'...other code & then...

    'check for open Outlook session & open if necessary
    'sourced from: Microsoft Office Help - Microsoft Office Discussion - Excel VBA Programming - Access Programming
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    On Error GoTo 0

    '### may need modification
    If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
        Set oNameSpace = OutApp.GetNamespace("MAPI")
        oNameSpace.Logon , , True
        oNameSpace.GetDefaultFolder(olFolderInBox).Display
        'WasOutlookOpenedByCode = True
    End If

    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)

'Dim OutApp As Object
'Dim OutMail As Object
'Set OutApp = CreateObject("Outlook.Application")
'Set OutMail = OutApp.CreateItem(0)

'    On Error Resume Next
'   ' Change the mail address and subject in the macro before you run it.
   With OutMail
        .SentOnBehalfOfName = "PGA Tour"
        .To = "Mickelson, Phil; Woods,Tiger"
        .CC = "Fowler, Rickie"
        .BCC = "Nicklaus, Jack" '
        .Subject = "Golf Weekly Update"
        .Body = "Blah, Blah Blah"
        .Attachments.Add (Fname)
        .Sensitivity = 2


        ' You can add other files by uncommenting the following line.
        '.Attachments.Add ("C:\test.txt")
        ' In place of the following statement, you can use ".Display" to
        ' display the mail.
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

ActiveWorkbook.Close savechanges:=True
'Excel.Application.Quit
'Outlook.Application.Quit
End Sub

Open in new window


What content are you looking to send?
Can you attach your Excel worksheet you are working with?
1
 
Sam CoombesAuthor Commented:
Thank you so much. I just need to send a couple of the cells of information. Which part of your code specifies which mailbox to use. As my outlook has 3 different email accounts and I need to select an account which email should be sent from.
0
 
Andrew WhiteCommented:
See Ron De Bruin's explanation of SendUsingAccount at http://www.rondebruin.nl/win/s1/outlook/account.htm
0
 
yo_beeDirector of Information TechnologyCommented:
Can you attach a sample of your excel worksheet so I can rework the code to meet your needs?
0
 
Sam CoombesAuthor Commented:
I don't need the attachment bit but I will work on changing that thank u very much.
0
All Courses

From novice to tech pro — start learning today.