Click here to Skip to main content
11,922,381 members (31,883 online)
Rate this:
Please Sign up or sign in to vote.
See more: VB MS-Excel macros email
Hi Guys,

I Have a scenario, in which i have a Master Sheet which contains employee names and their time sheet names along with their email'id's. (I am providing an attachment of the Sample Master Sheet)
this master sheet consists of all the time sheets of employees in it. if it is missing anyone of the employee's time sheet, then an automatic mail should be sent to that particular employee regarding the submission of Timesheet.

for example, In the sample Master sheet provided by me, there is no time sheet available for employee4 with the time sheet name "TS-employee4". so, in this case i want to send an automatic mail to the employee4.[^]
Posted 16-Apr-13 3:08am
Edited 16-Apr-13 3:13am
joshrduncan2012 16-Apr-13 11:25am
What have you tried (code-wise?) Where are you stuck? This isn't a well formed question.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Try something like this:
Option Explicit
Sub SendMail()
Dim sEmpName As String, sSheetName As String
Dim i As Integer
Dim srcWsh As Worksheet
On Error GoTo Err_SendMail
Set srcWsh = ThisWorkbook.Worksheets("Consolidate")
i = 5
Do While srcWsh.Range("A" & i) <> ""
    sEmpName = srcWsh.Range("D" & i)
    sSheetName = srcWsh.Range("D" & i)
    If EmpSheetExists(sSheetName) Then
        MsgBox "Send e-mail to: " & sEmpName, vbInformation, "Information..."
        'here code to send e-mail
    End If
    i = i + 1
    On Error Resume Next
    Set srcWsh = Nothing
    Exit Sub
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendMail
End Sub
Function EmpSheetExists(sEmpShName As String) As Boolean
Dim wsh As Worksheet, retVal As Boolean
On Error Resume Next
Set wsh = ThisWorkbook.Worksheets(sEmpShName)
retVal = (wsh Is Nothing)
Set wsh = Nothing
EmpSheetExists = retVal
End Function

[EDIT #1]
How to create new Outllok mail?
MailItem (Outlook 2003)[^]

Sub SendMyMail(sFromWho As String, sTo As String, sBody As String)
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
With myItem
    .SenderName = sFrom
    .To = sTo
    .Body = sBody
End With
'here place the code to:
' - close Outlook
' - clean up variables ;)
End Sub

kkcmania 17-Apr-13 1:14am
Yeah, this is something that i want. but, i also need the code to send email dude. Anyway, thank u for the code. it helps.
Maciej Los 17-Apr-13 3:05am
See my updated answer ;)
kkcmania 17-Apr-13 3:10am
Thank you Los, I will Check it out.
Maciej Los 17-Apr-13 3:23am
You're welcome ;)
BTW, Los it's my Surname, Maciej - Firstname ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 453
1 Dave Kreskowiak 335
2 Sergey Alexandrovich Kryukov 263
3 Richard MacCutchan 225
4 ppolymorphe 175
0 OriginalGriff 7,231
1 KrunalRohit 4,530
2 Sergey Alexandrovich Kryukov 3,356
3 George Jonsson 2,865
4 Suvendu Shekhar Giri 2,216

Advertise | Privacy | Mobile
Web01 | 2.8.151120.1 | Last Updated 17 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100