+ Reply to Thread
Results 1 to 5 of 5

VBA macro to send emails to list of recipients.

Hybrid View

GNTS VBA macro to send emails to... 02-24-2017, 12:19 PM
Logit Re: VBA macro to send emails... 02-24-2017, 12:38 PM
Arkadi Re: VBA macro to send emails... 02-24-2017, 12:45 PM
GNTS Re: VBA macro to send emails... 02-25-2017, 09:57 PM
Logit Re: VBA macro to send emails... 02-25-2017, 10:06 PM
  1. #1
    Registered User
    Join Date
    02-24-2017
    Location
    United States
    MS-Off Ver
    Office 2013
    Posts
    2

    VBA macro to send emails to list of recipients.

    Hello,
    I am new in excel macros and will appreciate any help will be provided. I have created excel spreadsheet that sends emails to the list of recipients. It works perfectly fine and captures all the cells needed, but for some reason removes graphs and pictures. I do need graphs and gadgets to go with the email as well. I have attached the spreadsheet for the review. In advance thank you for your help.
    Attached Files Attached Files

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,401

    Re: VBA macro to send emails to list of recipients.

    Perhaps this resource will help ? UNTESTED

    http://stackoverflow.com/questions/3...o-outlook-body

    You could also approach this in a different method: Create a pdf file of the worksheet, save it, then have your code refer to that saved file as an attachment.


    Option Explicit
    
    
    Sub EmailWithOutlook()
        Dim oApp As Object
        Dim oMail As Object
        Dim WB As Workbook
        Dim FileName As String
        Dim wSht As Worksheet
        Dim shtName As String
        Dim ThisFile As String
        
        ThisFile = "C:\Users\My\Desktop\Report.pdf"
        Application.ScreenUpdating = False
    
        With Range("A1:H65")
                ' Make a copy of the active worksheet
                ' and save it to a temporary file
                'I need this to only select rows A to H on the current sheet
                Sheets("Sheet1").Range("A1:H65").Copy                          '<--- Change Range to copy HERE
                Set WB = ActiveWorkbook
        End With
    
        'Create and show the Outlook mail item
        Set oApp = CreateObject("Outlook.Application")
        Set oMail = oApp.CreateItem(0)
        With oMail
            'Uncomment the line below to hard code a recipient
            'I need this to pull the "to" from Settings:A1
            .To = Sheets("Sheet1").Range("K1").Value                          '<--- Settings Sheet name here is Sheet1. Change as needed
            'Uncomment the line below to hard code a subject
            'I need this to pull the "Subject" from Settings:A2
            .Subject = Sheets("Sheet1").Range("K2").Value                       '<--- Settings Sheet name here is Sheet1. Change as needed
            'Uncomment the lines below to hard code a body
            'I need this to pull the "body" from Settings:A3
            .Body = Sheets("Sheet1").Range("K3").Value                          '<--- Settings Sheet name here is Sheet1. Change as needed
            .Attachments.Add ThisFile
            .Display
        End With
    
        'Restore screen updating and release Outlook
        Application.ScreenUpdating = True
        Set oMail = Nothing
        Set oApp = Nothing
    End Sub
    
    Sub CreatPDF()
    Dim Report As String
    
    Sheets("Sheet1").Range("A1:H65").ExportAsFixedFormat _
               Type:=xlTypePDF, _
               FileName:="C:\Users\My\Desktop\Report.pdf", _
               Quality:=xlQualityStandard, IncludeDocProperties:=True, _
               IgnorePrintAreas:=False, OpenAfterPublish:=True
               
    Call EmailWithOutlook
    End Sub
    Attached Files Attached Files
    Last edited by Logit; 02-24-2017 at 12:44 PM.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: VBA macro to send emails to list of recipients.

    The first problem lies in the fact that Sh.UsedRange is not picking up the charts as part of the usedrange. However, even if that is expanded, it will still have issues since they won't get copied, but maybe logit's solution resolves that?
    Last edited by Arkadi; 02-24-2017 at 12:54 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Registered User
    Join Date
    02-24-2017
    Location
    United States
    MS-Off Ver
    Office 2013
    Posts
    2

    Re: VBA macro to send emails to list of recipients.

    Thank you so much for the help.
    I tried the micro Logit has provided but unfortunately didn't work for my project. This might work if I was sending the same page to all the recipients, with the same info on it. In my situation metrics will be different for each associate and each associate will only receive their own data. In addition data will be large and be changing every day.
    Associate A,
    Associate B,
    Associate C...
    Appropriate email addresses in column A

    Thanks again

  5. #5
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    7,401

    Re: VBA macro to send emails to list of recipients.

    This will send an email with a specific attachment as listed on Sheet 1. You can change the Column references as needed.

    Option Explicit
    
    Sub Send_Files()
    'Working in Excel 2000-2016
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
        Dim OutApp As Object
        Dim OutMail As Object
        Dim sh As Worksheet
        Dim cell As Range
        Dim FileCell As Range
        Dim rng As Range
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set sh = Sheets("Sheet1")
    
        Set OutApp = CreateObject("Outlook.Application")
    
        For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    
            'Enter the path/file names in the C:Z column in each row
            Set rng = sh.Cells(cell.Row, 1).Range("C1:C10")
    
            If cell.Value Like "?*@?*.?*" And _
               Application.WorksheetFunction.CountA(rng) > 0 Then
                Set OutMail = OutApp.CreateItem(0)
    
                With OutMail
                    .to = cell.Value
                    .Subject = "Testfile"
                    .Body = "Hi " & cell.Offset(0, -1).Value
    
                    For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
                        If Trim(FileCell) <> "" Then
                            If Dir(FileCell.Value) <> "" Then
                                .Attachments.Add FileCell.Value
                            End If
                        End If
                    Next FileCell
    
                    .Send  'Or use .Display
                End With
    
                Set OutMail = Nothing
            End If
        Next cell
    
        Set OutApp = Nothing
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End Sub
    Attached Files Attached Files

+ 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. [SOLVED] Send multiple emails to multiple recipients
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 02-04-2017, 09:26 AM
  2. macro to send different sheets to different recipients as per list
    By rajeshturaha in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-28-2015, 12:37 AM
  3. [SOLVED] Macro To Send Emails with PDF: Multiple Emails and PDF's
    By totoga12 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-19-2014, 06:13 PM
  4. [SOLVED] VBA Send Email and Files to Recipients On List
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-18-2014, 03:40 AM
  5. [SOLVED] How to send Lotus email with VBA to more recipients and also to more copy recipients
    By Sachy in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-20-2013, 03:23 PM
  6. [SOLVED] Need Macro that will create & send emails w/ attachments from a list in Excel
    By rollerden in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2012, 07:02 PM
  7. 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

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