+ Reply to Thread
Results 1 to 3 of 3

Excel macro that will send emails from outlook

Hybrid View

  1. #1
    Registered User
    Join Date
    06-15-2012
    Location
    Manila, philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Excel macro that will send emails from outlook

    Hello guys! I got this VBA code from a colleague which he uses when sending multiple emails from Outlook. I don't have a programming background and I would like to ask for help on how to modify it. This code sends email based from the last cell. What I would like to do is send emails to all the recipients listed in column C. Column A will be the sender and column B will be email subject. Attached is the sample file.

    Sub Mail_Outlook()
    Dim OutApp As Object
    Dim OutMail As Object
    
     LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
     
     If Cells(LastRow, 1).Value <> "" Then
     
     MailTo = Cells(LastRow, 1).Offset(0, 2).Value
           
        MailSubject = Cells(LastRow, 1).Offset(0, 1).Value
        MailBody = "Regards," & vbNewLine & vbNewLine & "Sender Name"
    'Send Mail
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(o)
            With OutMail
                .Subject = MailSubject
                .To = MailTo
                .body = MailBody
                '.Attachments.Add FileNme
                .Display
                '.Send
            End With
            
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If
       
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Excel macro that will send emails from outlook

    This sends to all cells in column C, though my knowledge with e-mail is almost zero.

    Sub Mail_Outlook()
    Dim OutApp As Object
    Dim OutMail As Object, cel As Range
    
     LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
     
     If Cells(LastRow, 1).Value <> "" Then
     
    For Each cel In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(2)
           
        MailSubject = Cells(LastRow, 1).Offset(0, 1).Value
        MailBody = "Regards," & vbNewLine & vbNewLine & "Sender Name"
    'Send Mail
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(o)
            With OutMail
                .Subject = MailSubject
                .To = cel
                .body = MailBody
                '.Attachments.Add FileNme
                .Display
                '.Send
            End With
     Next
            Set OutMail = Nothing
            Set OutApp = Nothing
        End If
       
    End Sub

  3. #3
    Registered User
    Join Date
    06-15-2012
    Location
    Manila, philippines
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Excel macro that will send emails from outlook

    Hi AB33! I was using the template with the updated VBA code but I noticed that all emails have the same subject (from column B). How can I modify the code to ensure that the email subject will be what is listed in column B? Also, if possible, change the sender to what is in column A? Thanks!
    Last edited by terreese; 02-17-2014 at 10:05 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Outlook Macro to send recurring emails
    By Shanthuday in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 04-10-2013, 01:46 AM
  2. Send emails through Outlook from Excel
    By fodster in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2013, 11:16 AM
  3. [SOLVED] Bulk emails send use outlook & excel coding macro
    By sonu_kumar444 in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 12-26-2012, 08:06 AM
  4. Macro to re-send emails using Outlook
    By zainnofear1987 in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2012, 12:24 PM
  5. Can you tell Excel to send emails through Outlook?
    By Donald S in forum Excel General
    Replies: 1
    Last Post: 06-30-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1