send mail through Outlook RRS feed

  • Question

  • Hello there,

    i need help in mailing VBA, as i have a pivot table filtered via slicers, this pivot contain ID Tickets, Agent Mail, Leader Mail and the type of Ticket, 

    this type have a counted values.

    for ticket type: "Pending Client Update", there are some employee whom have a number for this type, i need to send a mail for each employee have a "Pending client update ticket" #, and put their leader mail in CC field, with high category, and subject titled "Pending client update tickets"

    and body

    Dear ("User"),


                          Kindly be informed that you have ("number that looked up this user in pending client update field at pivot") pending client update tickets, and you need to handle it as soon as possible,


    i need to use Send method instead of display method, send all the mails with one click, and at last want a msg with Yes/No option before sending the mail and also confirmation msg after sending the mails

    The attached sheet


    Thursday, November 20, 2014 7:46 AM

All replies

  • Hi Eugene,

    thanks a lot for your useful references, i'm really directed to

    and used the below Code but need some support as i'm not very familiar with mailing VBA 

    Sub Send_Row_Or_Rows_Attachment_1()
    'Working in 2000-2013
    'For Tips see:
        Dim OutApp As Object
        Dim OutMail As Object
        Dim rng As Range
        Dim Ash As Worksheet
        Dim Cws As Worksheet
        Dim Rcount As Long
        Dim Rnum As Long
        Dim FilterRange As Range
        Dim FieldNum As Integer
        Dim mailAddress As String
        Dim NewWB As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        On Error GoTo cleanup
        Set OutApp = CreateObject("Outlook.Application")
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        'Set filter sheet, you can also use Sheets("MySheet")
        Set Ash = ActiveSheet
        'Set filter range and filter column (column with names)
        Set FilterRange = Ash.Range("A1:D" & Ash.Rows.Count)
        FieldNum = 1    'Filter column = A because the filter range start in column A
        'Add a worksheet for the unique list and copy the unique list in A1
        Set Cws = Worksheets.Add
        FilterRange.Columns(FieldNum).AdvancedFilter _
                Action:=xlFilterCopy, _
                CopyToRange:=Cws.Range("A1"), _
                CriteriaRange:="", Unique:=True
        'Count of the unique values + the header cell
        Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))
        'If there are unique values start the loop
        If Rcount >= 2 Then
            For Rnum = 2 To Rcount
                'Look for the mail address in the MailInfo worksheet
                mailAddress = ""
                On Error Resume Next
                mailAddress = Application.WorksheetFunction. _
                    VLookup(Cws.Cells(Rnum, 1).Value, _
                              Worksheets("Mailinfo").Range("A1:B" & _
                                    Worksheets("Mailinfo").Rows.Count), 2, False)
                On Error GoTo 0
                If mailAddress <> "" Then
                    'Filter the FilterRange on the FieldNum column
                    FilterRange.AutoFilter Field:=FieldNum, _
                                           Criteria1:=Cws.Cells(Rnum, 1).Value
                    'Copy the visible data in a new workbook
                    With Ash.AutoFilter.Range
                        On Error Resume Next
                        Set rng = .SpecialCells(xlCellTypeVisible)
                        On Error GoTo 0
                    End With
                    Set NewWB = Workbooks.Add(xlWBATWorksheet)
                    With NewWB.Sheets(1)
                        .Cells(1).PasteSpecial Paste:=8
                        .Cells(1).PasteSpecial Paste:=xlPasteValues
                        .Cells(1).PasteSpecial Paste:=xlPasteFormats
                        Application.CutCopyMode = False
                    End With
                    'Create a file name
                    TempFilePath = Environ$("temp") & "\"
                    TempFileName = "Your data of " & Ash.Parent.Name _
                                 & " " & Format(Now, "dd-mmm-yy h-mm-ss")
                    If Val(Application.Version) < 12 Then
                        'You use Excel 97-2003
                        FileExtStr = ".xls": FileFormatNum = -4143
                        'You use Excel 2007-2013
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                    'Save, Mail, Close and Delete the file
                    Set OutMail = OutApp.CreateItem(0)
                    With NewWB
                        .SaveAs TempFilePath & TempFileName _
                              & FileExtStr, FileFormat:=FileFormatNum
                        On Error Resume Next
                        With OutMail
                            .To = mailAddress
                            .Subject = "New Account Pending Client Upadate Ticket"
                            '.Attachments.Add NewWB.FullName
                            .Body = "Dear " & vbNewLine & vbNewLine & _
                            "Kindly be informed that you have new account Pending Client Update Ticket, So please you are requested to handle it as soon as poosible" & vbNewLine & vbNewLine & _
                            "Thanks a lot"
                            .send  'Or use Send
                        End With
                        On Error GoTo 0
                        .Close savechanges:=False
                    End With
                    Set OutMail = Nothing
                    Kill TempFilePath & TempFileName & FileExtStr
                End If
                'Close AutoFilter
                Ash.AutoFilterMode = False
            Next Rnum
        End If
        Set OutApp = Nothing
        Application.DisplayAlerts = False
        Application.DisplayAlerts = True
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub

    when using an icon to activate the code, face an error in VBA module with a yellow highlighted line

                    FilterRange.AutoFilter Field:=FieldNum, _
                                           Criteria1:=Cws.Cells(Rnum, 1).Value

     and at the same time a new sheet contain the mails list is created automatic

    but when open the module and run the code is activated smoothly.

    also how can i add a list of mails at CC field

    the attached file

    thanks a lot, 

    Thursday, November 20, 2014 1:33 PM
  • > how can i add a list of mails at CC field

    See How To: Fill TO,CC and BCC fields in Outlook programmatically .

    Also you may find the Getting Started with VBA in Outlook 2010 page in MSDN helpful.

    Thursday, November 20, 2014 3:12 PM
  • Dear, 

    >> when using an icon to activate the code, face an error in VBA module with a yellow highlighted line

    unfortunately i can't get my issue solution after checked the mentioned URL 

    Saturday, November 22, 2014 3:55 PM
  • It seems the issue comes from the Excel object model and not related to Outlook at all. The subject of the thread doesn't correspond to the issue.

    What error do you get? Error code? Could you please be more specific? 

    Sunday, November 23, 2014 1:47 PM
  • Hi Eugene,

    what i need is sending mail to outlook from excel sheet, as want to mail a rows to each person in a range, when running the mentioned VBA code I faced an error with highlighted yellow color in that line 

                    FilterRange.AutoFilter Field:=FieldNum, _
                                           Criteria1:=Cws.Cells(Rnum, 1).Value

    but>> when using an icon to activate the code, face an error in VBA module with a yellow highlighted line

    the thread isn't related to the outlook, sorry if the title is fuzzy somehow ,

    thanks a lot for your following ,


    Sunday, November 23, 2014 3:07 PM
  • What property or method exactly generates the error?
    Sunday, November 23, 2014 3:28 PM