Email using multiple sheet in excel

[Realme]

Dear Mrexcel,

My query is can we send email through excel using multiple sheet.
For Example :
Excel File Contain
WorkSheet
Sheet 1 Sheet 2 Sheet 3 untill Sheet 100 all sheet has different email
This email is reminder as time sheet to the employee, so i would like to send email should go to employee as per his / her email
Likewise sheet 1 has employee A email address it means sheet 1 should go as an email to Employee A like Employee B and C and go and on and on.

Is there useful way to send email to various employees using multiple sheet.

This solution help lots of time saving.

Looking forward to hear from you.

Thanks,

Realme

 

[kevin9999]

A bit more information would have been helpful, such as: do you want every sheet sent every time? Also, where (in what cell) is the email address of the recipient, and what do you want the subject of the email to be, and what about the body of the email?

As a starting point, the following assumes you want every sheet sent every time, the email address is in cell A1 of each sheet, and the employee's name is in cell C1 of every sheet. All these things can be tweaked to suit your actual situation.

This code assumes you don't already have any files in the same directory as the file with the code that are called the same as the Employee name in cell C1.

Option Explicit
Sub Realme()
    Dim OutApp As Object, OutMail As Object
    Dim wb2 As Workbook, sh As Worksheet
    Dim EmailTo As String, Employee As String

    For Each sh In ThisWorkbook.Worksheets
        EmailTo = sh.Range("A1").Value
        Employee = sh.Range("C1").Value
        sh.Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Employee
        Set wb2 = ActiveWorkbook
       
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = EmailTo
            .Subject = "Reminder for " & Employee
            .Body = ""
            .Attachments.Add wb2.FullName
            .Send
        End With
       
        wb2.Close
        Kill ThisWorkbook.Path & "\" & Employee & ".*"
    Next sh
End Sub

 

[Realme]

Dear Kevin,

I would like to appreciate your quick response in regards of above query, well each sheet contain as follows :
Emp no.
Employee name
All date which employee did not attend work
Subject would absent days for specific month, like if we are sending absent days for March 2022 than subject would be absent days for March.
Email should attached each sheet related to employee no.
I hope this information is useful to find solution for my query.

Thank you once again and waiting for your response.

With best regards,

 

[kevin9999]

Dear Kevin,

I would like to appreciate your quick response in regards of above query, well each sheet contain as follows :
Emp no.
Employee name
All date which employee did not attend work
Subject would absent days for specific month, like if we are sending absent days for March 2022 than subject would be absent days for March.
Email should attached each sheet related to employee no.
I hope this information is useful to find solution for my query.

Thank you once again and waiting for your response.

With best regards,

It'll be impossible for me to progress this any further without seeing an actual copy of a 'typical' sheet. Please use the XL2BB Tool to provide one. You can disguise employee's name etc. to respect any privacy issues.

 

[Realme]

It'll be impossible for me to progress this any further without seeing an actual copy of a 'typical' sheet. Please use the XL2BB Tool to provide one. You can disguise employee's name etc. to respect any privacy issues.

Dear Kevin,

I have attached 2 sample file for your reference for working out on my query.

 

[kevin9999]

See if the following gives you what you want. You type the month/year into an Inputbox at the start of the code. You didn't tell me whether you wanted every sheet emailed every month, so I'll assume that you do.

Option Explicit
Sub Realme_2()
    Dim OutApp As Object, OutMail As Object
    Dim wb2 As Workbook, sh As Worksheet
    Dim EmailTo As String, Employee As String, month As String
    month = Application.InputBox("Enter Month & Year")
    
    For Each sh In ThisWorkbook.Worksheets
        EmailTo = sh.Range("B3").Value
        Employee = sh.Range("B2").Value
        sh.Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Employee
        Set wb2 = ActiveWorkbook
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = EmailTo
            .Subject = "Absent days for " & Employee & " for " & month
            .Body = ""
            .Attachments.Add wb2.FullName
            .Send
        End With
        
        wb2.Close
        Kill ThisWorkbook.Path & "\" & Employee & ".*"
    Next sh
End Sub

 

[Realme]

Dear Kevin,

Sorry for late reply.

Thanks for your reply clarification, yes i need to send all sheet every month but all sheet has different employee id and different time and month.

With best regards,

 

[Realme]

Dear Kevin,

I have another querry related to overtime. which is as under :-

Emp No. Date Time In Time Out
1 12-01-2022 7.30 08.00
1 12-01-2022 08.15 blank
1 12-01-2022 10.30 10.45
1 12-01-2022 11.15 13.45
1 12-01-2022 14.30 18.00

Here the challenge is we need to calculate overtime for more than 8 hours worked, some time shift timing is different start in mid nigh and end morning so there are mismatch of time sheet in and out for example

Shift timing is 24:00 end time 08.00 am so it calculate in minus how can we fix this issue.

I hope yo can provide solution for my querry.


With best regards,

 

[kevin9999]

Dear Kevin,

Sorry for late reply.

Thanks for your reply clarification, yes i need to send all sheet every month but all sheet has different employee id and different time and month.

With best regards,

So did you try the code in post #6? Did it work, and if not, what didn't it do?

 

[kevin9999]

Dear Kevin,

I have another querry related to overtime. which is as under :-

Emp No. Date Time In Time Out
1 12-01-2022 7.30 08.00
1 12-01-2022 08.15 blank
1 12-01-2022 10.30 10.45
1 12-01-2022 11.15 13.45
1 12-01-2022 14.30 18.00

Here the challenge is we need to calculate overtime for more than 8 hours worked, some time shift timing is different start in mid nigh and end morning so there are mismatch of time sheet in and out for example

Shift timing is 24:00 end time 08.00 am so it calculate in minus how can we fix this issue.

I hope yo can provide solution for my querry.


With best regards,

This is a completely different question, for which you should create a new thread.