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
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