Consulting

Results 1 to 6 of 6

Thread: Emailing excel sheets in a workbook to different recipients

  1. #1
    VBAX Newbie
    Joined
    Sep 2022
    Posts
    3
    Location

    Emailing excel sheets in a workbook to different recipients

    Hi All,

    I have found a code to send emails to multiple recipient based on the sheets name and its contents.
    the code is working fine but it require some amendments.

    i want to add column E (Email addres (CC) ) also i want to keep the information as a table with the preformatted column widths.

    I have attached the sample sheet & image of the required output.

    hope someone will help me to find a solution.

    Thanks

    Sub email_location()
    Dim lRow As Long
    Dim sBody, y As Long, c
    Dim sq(), ar, x As Long, j As Long, jj As Long
    lRow = Worksheets("Email Address").Cells(Rows.Count, 4).End(xlUp).Row
    For Each c In Worksheets("Email Address").Range("D2:D" & lRow).Cells
       sBody = ""
       y = 0
       x = 0
       ReDim sq(x)
       ar = Sheets(c.Value).UsedRange
          For j = 1 To UBound(ar)
             For jj = 1 To UBound(ar, 2)
                 If ar(j, jj) <> "" Then
                     ReDim Preserve sq(x)
                     sq(x) = ar(j, jj)
                     x = x + 1
                 End If
             Next
         Next
          sBody = "hi, " & c.Offset(0, -3).Value
          Do While y < x
          sBody = sBody & vbNewLine & sq(y)
          y = y + 1
          Loop
        With CreateObject("outlook.application").CreateItem(0)
           .To = c.Offset(0, -1).Value
           .Subject = c.Offset(0, -3).Value & " " & c.Offset(0, -2).Value & " - " & c.Value
           .Body = sBody
           '.Attachments.Add
           .Display '.send
         End With
    Next
    End Sub
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    maybe insert in you code:

    .CC = c.Offset(0,1).Value & ""

  3. #3
    VBAX Newbie
    Joined
    Sep 2022
    Posts
    3
    Location
    Hi,

    It works. thanks

    is it possible to keep the same template with the hyperlink in the email(as attached )

  4. #4
    if you are going to use Hyperlink, then suggest you change from .Body to .HtmlBody.
    also you google how to add hyperlink Tag to html, i think it is more on

    <a href="the address to the link">the text to display</a>

  5. #5
    VBAX Newbie
    Joined
    Sep 2022
    Posts
    3
    Location


    Is anyone there to help..

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,312
    Location
    Did you try arnelgp's suggestion, and if so, what was the response?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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