Send Mail from Excel to Outlook

[MaunishP]

Hi Team,
I was going through many articles in this forum to check VBA code to send mail from Excel to Outlook directly. I got a great link http://www.rondebruin.nl/win/s1/outlook/bmail3.htm, however this still doesnt full fill my requirement.

This is a snapshot what my requirement are



I have an excel file each month with three different worksheet. I need to send emails to my vendor at every bi-hour regarding today's work performed. Copying and pasting is easy but a monotonus work. I would like to have a VBA code which will select data by going into all 3 worksheets selecting today's data and sending 4 different emails.

I have attached sample excel file which i require.

Regards,
Maunish Patel

 

[MaunishP]

Hi Team,
Just to add-in i found this code, however can you also add requirement which i have said - 4 different mails from 3 different worksheets and data need to be pasted for today's date only.

I have attached excel which i found.

Sub Preview()

I = Cells(2, "B").Value   ' dynamising startrownumber to user fed value at cell B2

Do           ' start the action ,buddy!!!!

Subj = Cells(I, "A").Value
Filepath = Cells(I, "B").Value
EmailTo = Cells(I, "C").Value
CCto = Cells(I, "D").Value
msg = Cells(I, "E").Value

Application.DisplayAlerts = False ' hey macro ,i dont wanna make you take time ,so this command to save time to avoid displays

Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .To = EmailTo
        .CC = CCto
        .BCC = ""
        .Subject = Subj
        .body = msg
        .Attachments.Add Filepath
        .display
            
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

Application.DisplayAlerts = True

I = I + 1

Cells(1, "A").Value = "Outlook sent Time,Dynamic msg preview  count  =" & I - 3

Loop Until Cells(I, "C").Value = ""


End Sub

Regards,
Maunish Patel