help with send an Email with Excel VBA Code

eltyar

New Member
Joined
Mar 16, 2017
Messages
1
please how can i say in this code... send mail when you see "ok" in cells of column "P" and if any empty cells ignore it and continue sending mails?
here's the Code:

Code:
    Sub sendmail()
I = 4
Do




EmailTo = Cells(I, "J").Value
CCto = Cells(I, "K").Value
Subj = Cells(I, "L").Value
Filepath = Cells(I, "M").Value
msg = Cells(I, "N").Value




Application.DisplayAlerts = False


Dim OutApp As Object
    Dim OutMail As Object
    Dim SigString As String
    Dim Signature As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    SigString = Environ("appdata") & _
                "\Microsoft\Signatures\mrm.htm"


    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
    On Error Resume Next
    With OutMail
        .To = EmailTo
        .CC = CCto
        .BCC = ""
        .Subject = Subj
        .HTMLBody = msg & "<br>" & Signature
        '.body = msg & vbNewLine & vbNewLine & Signature
        .Attachments.Add Filepath
        .Display    '.Send   'or use .Display


    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing






Application.DisplayAlerts = True


I = I + 1
Cells(1, "P").Value = "Outlook sent Time,Dynamic msg preview  count  =" & I - 3
Loop Until Cells(I, "P").Value = "ok"




End Sub








Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function


i'm really appreciate your help in advance. thanks.
Excel sheet is attached
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,215,491
Messages
6,125,108
Members
449,205
Latest member
ralemanygarcia

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