Send bulk email from Excel issues: 1-Attach multiple files with file paths, 2-Copy visible ranges and 3-Add default email signature keeping formats

[randaubienghoc]

Hi everyone,

As a new VBA learner, I am seeking for your help to solve these three problems when automatically send Outlook email from Excel. The code is now lacking these improvements. Your support would be highly appreciated.

1. Attach multiple files with file paths by criteria (For same customer codes/ subject, send the same email and attach respective files followed their paths in column, skipping the rows with status "Y")

2. Copy and paste to email visible data ranges from Excel keeping formats by criteria (only copy VISIBLE table from column I to O keeping the same format, skipping the rows with status "Y")

3. Add default Outlook email signature at the end of email body keeping formats (insert signature without changing formats).

Please see my macro file and test files to be sent to email & expected first email illustration.

Thank you very much.

Option Explicit

Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.ActiveSheet

Dim i As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))

For i = 2 To last_row
Set msg = OA.createitem(0)
msg.To = sh.Range("A" & i).Value
msg.cc = sh.Range("B" & i).Value
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value

If sh.Range("E" & i).Value <> "" Then
    msg.attachments.Add sh.Range("E" & i).Value
End If


msg.display

sh.Range("F" & i).Value = "Sent"

Next i

MsgBox "All the mails have been sent successfully"


End Sub