+ Reply to Thread
Results 1 to 3 of 3

Sending automated emails from excel using VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    06-29-2015
    Location
    Tallinn, Estonia
    MS-Off Ver
    Excel 2013
    Posts
    7

    Unhappy Sending automated emails from excel using VBA

    Hey all,

    So i am trying to send automatic emails using excel source information and VBA code. However I cannot seem to put it together. I searched this forum and found this http://www.excelforum.com/excel-prog...-in-range.html

    This kinda works, BUT for every line - which is not desirable. I do not want it to send email for every line i want to summarize it by email but - and send only like 15 emails instead of i.e 65 emails for every line.

    How can it be done, please help.

    My table looks like this:

    Period Number Provider Service Count Duration Total Email
    201504 31987 ETV Phone 12 23 23432 [email protected]
    201504 31987 ETV Phone 12 23 23432 [email protected]
    201504 31987 STV Phone 12 23 23432 [email protected]
    201504 31987 STV Phone 12 23 23432 [email protected]


    And code from the other thread looks like this.

    Sub email()
        Dim i As Long
        Dim OutApp, OutMail As Object
        Dim strto, strcc, strbcc, strsub, strbody As String
        
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
           
        For i = 1 To Range("c65536").End(xlUp).Row
            Set OutMail = OutApp.CreateItem(0)
                    
                strto = Cells(i, 8)
                strsub = "Missing Books"
                strbody = "Good day," & vbNewLine & _
                    "We are missing below books:" & vbNewLine & _
                    Cells(i, 1) & Chr(32) & Cells(i, 2) & Cells(i, 3) & Cells(i, 5) & Cells(i, 6) & vbNewLine & vbNewLine & _
                    "brgds," & vbNewLine & _
                    "Alex"
                    
                With OutMail
                    .To = strto
                    .Subject = strsub
                    .Body = strbody
                    '.Send
                    .display
                                    
                End With
                On Error Resume Next
            
        Next
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    End product should look like this:


    message TO [email protected]

    Hello,

    Here is the report:
    Period Number Provider Count Duration
    201504 31987 STV 12 23
    201504 31987 STV 12 23

    Best,
    Annux
    Last edited by annux3; 06-30-2015 at 07:30 AM. Reason: Solved

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Sending automated emails from excel using VBA

    Try this...

    Sub email()
        Dim Lr As Long
        Dim rngEmails As Range, rngEmail As Range, rngLine As Range
        Dim OutApp As Object, strbody As String
        
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
        
        Application.ScreenUpdating = False
        
        Lr = Range("H" & Rows.Count).End(xlUp).Row
        Range("H1:H" & Lr).AdvancedFilter xlFilterInPlace, Unique:=True
        Set rngEmails = Range("H2:H" & Lr).SpecialCells(xlCellTypeVisible)
        If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
           
        For Each rngEmail In rngEmails
        
            Range("H1:H" & Lr).AutoFilter 1, rngEmail.Value
                    
            strbody = "Good day," & vbNewLine & "We are missing below books:" & vbNewLine
            For Each rngLine In Range("A2:A" & Lr).SpecialCells(xlCellTypeVisible)
                strbody = strbody & rngLine & Chr(32) & rngLine.Offset(, 1) & _
                          rngLine.Offset(, 2) & rngLine.Offset(, 4) & rngLine.Offset(, 5) & vbNewLine
            Next rngLine
            strbody = strbody & vbNewLine & "brgds," & vbNewLine & "Alex"
                
            With OutApp.CreateItem(0)
                .To = rngEmail.Value
                .Subject = "Missing Books"
                .Body = strbody
                '.Send
                .Display
            End With
            
        Next
        
        ActiveSheet.AutoFilterMode = False
        Application.ScreenUpdating = True
        Set OutApp = Nothing
        
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    06-29-2015
    Location
    Tallinn, Estonia
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Sending automated emails from excel using VBA

    Oh wow! It actually worked just like I wanted!

    You are my savior.

    Many thanks!!!

+ 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. Sending Emails Using Excel & VBA
    By dthennessy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2014, 04:07 AM
  2. Automated Emails from Excel Pivot table
    By cicapopsi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-15-2013, 05:49 AM
  3. [SOLVED] Sending Outlook emails from Excel; Limits to three emails only?
    By BPSJACK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2013, 06:53 AM
  4. Is it possible to send automated emails from excel to outlook web app.
    By jayant24 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-24-2013, 06:14 AM
  5. Sending macro emails using excel: Send emails with their passwords.
    By loveisblind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2009, 03:16 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