Consulting

Results 1 to 2 of 2

Thread: Email every sheet

  1. #1
    VBAX Regular
    Joined
    Feb 2018
    Posts
    70
    Location

    Email every sheet

    Hi, I am using the Ron de Bruin code to email every sheet with the address in A1. I've a slight problem as I have 150 sheet and there are 12 recipients. I'd like each email address to receive one email with each attachment, rather than 150 different emails going off and people receiving multiple email. Is this possible and what would the coding be? Many thanks

    Sub Mail_Every_Worksheet() 'Working in Excel 2000-2016 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object TempFilePath = Environ$("temp") & "" If Val(Application.Version) < 12 Then 'You use Excel 97-2003 FileExtStr = ".xls": FileFormatNum = -4143 Else 'You use Excel 2007-2016 FileExtStr = ".xlsm": FileFormatNum = 52 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject("Outlook.Application") For Each sh In ActiveWorkbook.Worksheets If sh.Range("A1").Value Like "?*@?*.?*" Then sh.Copy Set wb = ActiveWorkbook TempFileName = "Sheet " & sh.Name & " of " _ & ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss") Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum On Error Resume Next With OutMail .to = sh.Range("A1").Value .CC = "" .BCC = "" .Subject = "This is the Subject line" .Body = "Hi there" .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add ("C:\test.txt") .Display 'or use .Display End With On Error GoTo 0 .Close savechanges:=False End With Set OutMail = Nothing Kill TempFilePath & TempFileName & FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    This is nothing more than a continuation of the discussion already under way in http://www.vbaexpress.com/forum/show...il-every-sheet
    Kindly don't start multiple threads on the same topic or a related topic. Thread closed. You may continue the discussion in your original thread.
    And, when you do need to start a new thread dealing with Integration/Automation of Office Applications, kindly post in the appropriate forum.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

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