Send Multiple (Outlook) AutoMail Using VBAMacro with Multiple attachment & Recipients

akash121

Board Regular
Joined
Apr 21, 2013
Messages
58
Hello Friend,

In the below code i have try to build a Macro to send automatic mail. Trust me i am not good in Macro.

Based on Column C (Triger). Means if it says "Yes" Then only mail should go out. else it should skip and move to next row(report).
Column E = are the Multiple receiver seperated by (; )
Column F = CC Multiple seperated by (; )
Column G = BCC seperated by (; )
Column H = Subject line of Mail should be copied for each report seperately
Column I = Body of the Mail
Column J = Error desciption whether mail sent successfully or failed
From column K to P are the File names and File path for the attachment.
in the End signature should also be added which is available in outlook.


I have built the incomplete macro not which may not be proper as of now its sending mail but not as per my requirement above.

Please help

Thank you
Akash


Below is the code

Sub sendreminder()
Dim outlookapp As Object
Dim outlookmailitem As Object
Dim icounter, icountercc As Integer
Dim maildest, mailcc, floc As String
Dim vfile As Variant, vfiles As Variant
Dim cell As Range, rng As Range

With ThisWorkbook.ActiveSheet
Set rng = Range("F3", .Range("F" & Row.Count).End(xlUp))
End With

Set outlookapp = CreateObject("outlook.application")
Set outlookmailitem = outlookapp.createitem(0)
With outlookmailitem
maildest = ""
For icounter = 1 To WorksheetFunction.CountA(Columns(5))
If maildest = "" And Cells(icounter, 5).Offset(0, -2) = "Yes" Then
maildest = Cells(icounter, 5).Value
floc = cell.Offset(2, 11).Value
If Right(floc, 1) <> "\" Then floc = floc & "\"
Next vfile
ElseIf maildest <> "" And Cells(icounter, 5).Offset(0, -2) = "Yes" Then
maildest = maildest & ";" & Cells(icounter, 5).Value
End If
Next icounter
.cc = Range("F3").Value
.bcc = maildest
.Subject = Range("H3").Value
.Body = Range("I3").Value
.send
End With
setoutlookmailitem = Nothing
Set outlookapp = Nothing

End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi Michael,

Thank you for responce & sharing the link

I have already seen the link but main issue which i am facing with my code is how to set Rule that only if its Today date & Column C says "Yes" then send the mail if "No" skip and move to next Row.

Thank you
Akash
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top