+ Reply to Thread
Results 1 to 5 of 5

VBA Split wb tabs & send each tab out to email recipient in A1-add body to email

  1. #1
    Registered User
    Join Date
    02-28-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    3

    VBA Split wb tabs & send each tab out to email recipient in A1-add body to email

    Hi,

    I am using Excel 2010 and Outlook 2010 and have some VBA that allows me to split out my workbook of about 40 tabs and email each tab as an attachment to the recipients in cell A1 of each tab. I would like to add body to my email though and am completely stumped, please help!

    This is what I have so far and it works but I can't get text into my email body:

    Sub Mail_Every_Worksheet()
    'Working in 97-2010
    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 I As Long

    TempFilePath = Environ$("temp") & "\"

    If Val(Application.Version) < 12 Then
    'You use Excel 97-2003
    FileExtStr = ".xls": FileFormatNum = -4143
    Else
    'You use Excel 2007-2010
    FileExtStr = ".xlsm": FileFormatNum = 52
    End If

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    For Each sh In ThisWorkbook.Worksheets
    If sh.Range("A1").Value Like "?*@?*.?*" Then

    sh.Copy
    Set wb = ActiveWorkbook

    TempFileName = "Detailed Monthly Payroll Report " & "for " & sh.Name

    addressees = Split(Range("A1"), ",")

    With wb
    .SaveAs TempFilePath & TempFileName & FileExtStr, _
    FileFormat:=FileFormatNum
    On Error Resume Next
    For I = 1 To 3
    .SendMail addressees, "Monthly Payroll Report for Cost Centre " & sh.Name
    If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
    .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    End If
    Next sh

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    End Sub


    Thanks for your help!

  2. #2
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: VBA Split wb tabs & send each tab out to email recipient in A1-add body to email

    Quote Originally Posted by LucyA View Post
    I would like to add body to my email though and am completely stumped, please help!

    .SendMail addressees, "Monthly Payroll Report for Cost Centre " & sh.Name
    SendMail doesn't allow email body text to be specified. You could use the Outlook object model instead - see http://www.rondebruin.nl/sendmail.htm for examples.

  3. #3
    Registered User
    Join Date
    02-28-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VBA Split wb tabs & send each tab out to email recipient in A1-add body to email

    Hi,

    Thanks for that. This looks exactly like what I want. I can't get the text to appear in the first email, but it appears in all the subsequent ones. I now have this:

    Sub Mail_Every_Worksheet()
    'Working in 2000-2010
    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-2010
    FileExtStr = ".xlsm": FileFormatNum = 52
    End If

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    Set OutApp = CreateObject("Outlook.Application")

    For Each sh In ThisWorkbook.Worksheets
    If sh.Range("A1").Value Like "?*@?*.?*" Then

    sh.Copy
    Set wb = ActiveWorkbook

    TempFileName = "2012-02 Payroll Report " & "for " & sh.Name

    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 = "Detailed Payroll Report for Cost Centre " & sh.Name
    Dim strbody As String
    .Body = strbody
    strbody = "Hi," & vbNewLine & vbNewLine & _
    "Please kindly find the attached monthly payroll breakdowns for your relevant cost centre(s) to complement your P&L analysis. Please kindly advise of any queries before COB Thursday 1st March, 2012 to either myself or your accountant." & vbNewLine & _
    "Monthly payroll reports include Annual Leave and TOIL balances. However, please note the following:" & vbNewLine & _
    "Pay runs do not necessarily fall on the last Friday of every month end as 5 week months cause a timing difference." & vbNewLine & vbNewLine & _
    "Kind Regards," & vbNewLine & vbNewLine & _
    "Lucy"


    .Attachments.Add wb.FullName
    'You can add other files also like this
    '.Attachments.Add ("C:\test.txt")
    .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


    Thanks so much, I really appreciate your help!
    Last edited by LucyA; 02-28-2012 at 08:59 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    05-21-2009
    Location
    Great Britain
    MS-Off Ver
    Excel 2003
    Posts
    550

    Re: VBA Split wb tabs & send each tab out to email recipient in A1-add body to email

    Quote Originally Posted by LucyA View Post
    I can't get the text to appear in the first email, but it appears in all the subsequent ones.
    Because you assign strbody to .Body before defining it. To fix this, move the .Body = strbody line below the strbody = statement. Or, because your strbody text is fixed, move it and the 'Dim strbody As String' outside the loop.

  5. #5
    Registered User
    Join Date
    02-28-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: VBA Split wb tabs & send each tab out to email recipient in A1-add body to email

    Perfect, this works really well. Thanks so much! Really appreciate the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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