Worksheet range as email body

  • Hello,

    I am a Newbie in Excel Macros and would like to loop though my worksheets, set a range and have this displayed as an email body.

    Using Ron de Bruin macro, I am able to display this range of the current workbook.

    The full macro does the following:

    1. Loops through all worksheets and checks if cell B1 contains an email address.

    2. If sheet contains email address then a dynamic print range is set up and coverts this to a pdf

    3. The pdf is emailed to <<cell b1>>

    4. The email body then looks at the current worksheet and displays data under J2:S17 -> this is what I need to change

    I need the system at the point of defining the email address and attaching the correct pdf also have Range J2:S17 of the relevant worksheet to display as part of the email body.

    The below code shows part of the macro.

  • Thanks. I have already Incorporated that function.

    I have managed to add a cell value to the email body .

    1. sMsgBody = sh.Range("j2")

    Is there a way I can add show the full range. The below gives run time error

    1. sMsgBody = sh.Range("j2:s17")
  • thanks for the link. I tried to incorporate the code from the link however it shows the current worksheet range.

    I also ran the link standalone - this also only shows me the active worksheet data.

    I would like to go through each worksheet and display the range as the email body as a different email. (not just the current active worksheet)

  • Your code is looping through each sheet

    1. For Each sh In ThisWorkbook.Worksheets
    2. Set rng = Nothing
    3. On Error Resume Next
    4. Set rng = Range("J2:S17").SpecialCells(xlCellTypeVisible)
    5. Next sh

    That code will always use the ActiveSheet, which is why I suggested that you make the change to

    1. For Each sh In ThisWorkbook.Worksheets
    2. Set rng = Nothing
    3. On Error Resume Next
    4. Set rng = Sh.Range("J2:S17").SpecialCells(xlCellTypeVisible)
    5. Next sh

    However, from your last reply I think you need to change the code to this.

  • Thanks Roy however that code doesnt work either.

    This code loops through each sheet however only shows the current active worksheet range as the email body. (not the individual worksheet ranges)

    It also loops through each sheet (for example 7 worksheets) and then attaches the same pdf file ".Attachments.Add FileNamePDF" for seven times.

  • Your function

    Function RDB_Mail_PDF_Outlook is looping through the sheets.

    Each time it loops it replaces the body with the next sheet range and attaches another pdf to the same email.

    You need to find a way to transfer the from MainMacro to the function and then you won't have to do a sh loop inside the function.