Need a macro to make workbooks based on employee names

Try this code - change the bold area to match your system -

Sub copy_data()
Dim shno As Long
Dim lrow As Long
Dim i As Long
Dim empfile As String
Dim wk As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ThisWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"

With ThisWorkbook.Worksheets("Temp")
    ThisWorkbook.Worksheets(1).Columns(2).Copy .Range("A1")
    .Columns("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        empfile = "B:\Test\" & .Range("A" & i).Value & ".xlsx"
        ThisWorkbook.Worksheets(1).Rows(1).AutoFilter field:=2, Criteria1:=.Range("A" & i).Value
        Set wk = Workbooks.Add
        wk.SaveAs Filename:=empfile, FileFormat:=51
        ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
        wk.Worksheets(1).Range("A1").PasteSpecial (xlPasteValuesAndNumberFormats)
        Application.CutCopyMode = False
        wk.Worksheets(1).Rows(1).Font.Bold = True
        wk.Worksheets(1).Cells.EntireColumn.AutoFit
        wk.Save
        wk.Close
    Next i
End With

ThisWorkbook.Worksheets(1).Rows(1).AutoFilter
ThisWorkbook.Worksheets("Temp").Delete

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Hi renjithvakkayil

Where will the Email Address be stored?

Hi renjithvakkayil

Post a sample...I'll look at it.

in the same folder I can have an excel workbook with Column A containing Employee names and B containing the corresponding email IDs

Thank you Renjith. Am glad it worked for you. John should be able to help you. I have not done coding with outlook...still have to learn that.

Hi renjithvakkayil

What do you currently send the Employee? Their Workbook as an attachment? Their Worksheet as an attachment? Their Worksheet as a PDF? Their Worksheet in the Body of the Email? Need to know what approach you wish to use.

Hi renjithvakkayil

Do all your Employees have Excel (2007 or later if .xlsx)? Will they be able to open an Excel File?
Would not a .PDF attachment be better?
Alternately, the Worksheet could be embedded in the Body of the Email such that there would be no need for Excel or Acrobat.
You tell me.

Hi renjithvakkayil

Let me play with it a bit...

Hi renjithvakkayil

The attached File has Code to do this

an outlook macro to mail these workbooks to employees without me having to manually do the same

and this

the Worksheet could be embedded in the Body of the Email

I modified arlu's Code only very slightly.

There are certain things you need to do. In Module Public_Variables you need to change these two items

'This is where the Employee Files are stored
Public Const myPath As String = "E:\Test\"

 'This is where the Employee Email Addresses are stored
 'This File should be stored in in the same Folder as the Employee Files
 'such that in this example EBook.xls will be located in E:\Test\
Public Const EBook As String = "EBook.xls"

EBook.xls needs to be maintained...you need to do this manually...if an Employee has not an Email Address in this file, you'll get a warning message but the Employee will not get an Email. Be diligent in maintaining this file.

In MailBooks Module I'd STRONGLY urge you to set the Code to display in the Macro Sub Mail_Sheet_Outlook_Body() as you're testing...do that here

With OutMail
                .to = EmpCel.Offset(0, 1).Value
                .CC = ""
                .BCC = ""
                .Subject = "Attendance Record"
                .HTMLBody = RangetoHTML(rng)
                .Send   'or use .Display '<---- to send
                '            .Display    '<---- to display
            End With

The Book is attached...let me know of issues.

Hi John the macro is running but n o mails are going out...I saved the Ebook with only my name in it...is that the problem ? Should i put mail IDs to all the files? I mean if a file doesnt have an ID only that mail wont go right ? Rest will go...

Hi renjithvakkayil

I should have attached the EBook File I'm using for testing. Make certain your file has the same structure, including the Headers.