Consulting

Results 1 to 5 of 5

Thread: Send multiple columns and rows by e-mail vba

  1. #1

    Send multiple columns and rows by e-mail vba

    Hello,

    testemail.xlsmtestemail.xlsm

    Few things,

    First : I would like the "table" from G2 to K5 to not show up rows and/or columns if the entire column or row are all equal to 0. In this case above, pig column should not show up. IF Ocean row would be at 0, then do not show Ocean row... but in this case, there is a 7.

    Second : I would like vba to make only G1 and G14 e-mail links, instead of the entire column (Currently the macro causes every cell in the column to be hyperlinks).

    Third : I would like to send that "table" from G2 to K5 to the first "Send e-mail" link.

    How would I be able to do that ?
    Attached Files Attached Files

  2. #2
    testemail.xlsm

    #2 is done. I was able to make only G1 and G14 e-mail links. I am posting the sheet in case somebody in interested in the script.

    Someone sent me a link on how to do #3, but I am still slightly confused. I don't know how to reference libraries or anything like that. I am not sure if there is an alternative. (Under Sub Send_The_Emails_1())

  3. #3
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    From VBA Editor toolbar:

    Tools > References > scroll down until you find Microsoft Outlook x.x Object Library and check it. Scroll down some more until you see Microsoft Word x.x Object Library and check it. Click OK.

    It is possible to build email body without the Word object.

    https://www.exceldemy.com/macro-to-s...il-from-excel/

    Need to adjust cell range referenced in code for the emailRange variable.

    Cell formulas are also messed up.

    As for not including columns that total to 0, why not just include all? But consider:

    Add a row that calculates column totals and reference in code.
    Sub HideCols()
    Dim x As Integer
    With Worksheets("Sheet1")
    For x = 9 To 12
        If .Cells(6, x) = 0 Then
            .Columns(x).Hidden = True
        End If
    Next
    End With
    End Sub
    Last edited by June7; 06-23-2024 at 05:23 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Thank you very much on how to add the references. It works !
    I noticed I messed up the formulas/ranges after I moved the "table". In my real workbook, everything is okay.

    As for the reason why I want to hide rows/columns, that "table" must be sent by e-mail. The workbook that my team will be working on has way more entries. We just want to make it as least confusing and as simple as possible for them to read. A PivotTable seemed like a good idea, but if I were to add more entries other than Air, Ocean, Ground and refresh the PivotTable, it would not add the fourth option without me selecting it manually from the filter and it would also not refresh automatically, so I wanted another option.

    Maybe I am going the wrong way about this. It looks like if I were to do the same for rows, that some of the information on the left would be gone (hidden), and adding new information does not automatically unhide any columns or rows.

  5. #5
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    Code can unhide columns as well as hide.

    Yes, managing dynamic pivoted data is tricky.

    I am mainly an Access user. Whatever you are doing to manage data in Excel I would probably do in Access.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Posting Permissions

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