Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB 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.
 
http://www.sendspace.com/file/lz7vji[^]
Posted 16-Apr-13 3:08am
Edited 16-Apr-13 3:13am
v2
Comments
joshrduncan2012 at 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
good
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
Loop
 
Exit_SendMail:
    On Error Resume Next
    Set srcWsh = Nothing
    Exit Sub
    
Err_SendMail:
    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
    .Send
End With
 
'here place the code to:
' - close Outlook
' - clean up variables ;)

End Sub
 
SenderName[^]
To[^]
Body[^]
BodyFormat[^]
Send[^]
[/EDIT]
  Permalink  
v2
Comments
kkcmania at 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 at 17-Apr-13 3:05am
   
See my updated answer ;)
kkcmania at 17-Apr-13 3:10am
   
Thank you Los, I will Check it out.
Maciej Los at 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)



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 17 Apr 2013
Copyright © CodeProject, 1999-2014
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