Need a macro to make workbooks based on employee names

[renjithvakkayil]

Hi Friends I am attaching an Excel with attendance record of employees...I would like to have a macro to create workbooks based on employee names..ie for each employee there should be a workbook which contains his/her attendance data...Also please advice if I can have an outlook macro to mail these workbooks to employees without me having to manually do the same...

[arlu1201]

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

Please Login or Register  to view this content.

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

[renjithvakkayil]

Hi Arlette your code is perfect...I cant give you rep because my last rep was to you..but soon will give you one...btw can u also advice me regarding the macro for outlook ??

[renjithvakkayil]

Hi Friends Arlette has given a perfect solution for the first part of my request...can anybody advice me regarding the outlook macro part ? If its impossible then I can close the thread as SOLVED...

[jaslake]

Hi renjithvakkayil

Where will the Email Address be stored?

[renjithvakkayil]

Hi Jaslake in the code provided by Arlette I am modifying the folder where I want to save the newly created sheets...

empfile = "B:\Test\" & .Range("A" & i).Value & ".xlsx" (I will make the change as D:\EMPLOYEEATTENDANCE) in the same folder I can have an excel workbook with Column A containing Employee names and B containing the corresponding email IDs...will that be okey??

[jaslake]

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

[arlu1201]

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.

[renjithvakkayil]

Hi Jaslake when I run the macro the folder specified will be populated with worksheets named as per employee names..ie each name in the worksheet attached in my first post will have a corresponding excel sheet with only his/her data..

In that folder I can save the attached excel..the macro should create mail according to the email ID and attach the corresponding excel and send it...

[jaslake]

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.

[renjithvakkayil]

We are not currently sending them anything as its time consuming..but now after the macros we are planning to send them the sheet as an attachment(xls or slsx) with subject attendance record.

[jaslake]

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.

[renjithvakkayil]

Hi Jaslake that's a good point..few have 2007 and others 2010 and some dont have Acrobat..so I think the last method you mentioned will be okey for all...

[jaslake]

Hi renjithvakkayil

Let me play with it a bit...

[renjithvakkayil]

Thanks John!!

[jaslake]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

[renjithvakkayil]

Hi John today I cannot access the office mail..as its our weekly OFF day(Being in Middle East we have FRIDAY holiday)...will check the code tomorrow and let you know how it went..Thanks a lot for helping me out...

[renjithvakkayil]

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...

[jaslake]

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.

[renjithvakkayil]

Hi John is it okey if I dont put email accounts against some name ? Because some users in the list are not bound by the attendance timings so they dont need to follow that...the mail should be send to all users for whom the ID is present and others should be left as it is...

I checked the code and it works fine but for each mail OUTLOOK is giving a message"A program is trying to send an e-mail message on your behalf" ALLOW/DENY....I have to click allow for each user...can I bypass this ?

[jaslake]

Hi renjithvakkayil

What version of Outlook are you using?

for each mail OUTLOOK is giving a message"A program is trying to send an e-mail message on your behalf"

Regarding this

is it okey if I dont put email accounts against some name

I'll need to modify the Code to accommodate this...give me a bit.

Question...if an Employee doesn't have an Email Address in the EBook File, do you still want a Workbook created for that employee? If so, why...will you do anything with it or is it just taking up space?

Further question...why are we creating all these Individual Workbooks and not just one Workbook with a separate sheet for each Employee?

[renjithvakkayil]

Hi John I tested using Outlook-2013 but in my office I will be using 0ffice-2010 (will be testing today)...The employees for whom the workbook doesn't need to be sent doesn't need a workbook but the issue is classifying such employees..if someone from our branch office comes their ID will also be captured and a sheet will be made for them also but I may not need that...so the best I can do is delete the unwanted sheets once its created and send mails to only relevant ppl(after mailing I will delete those sheets also).


If its a single workbook with sheets then each employee will have access to the timings of others and the management doesn't want this.If its individual sheet then ppl will see only their data.

[jaslake]

Hi renjithvakkayil

If you're running Outlook 2007 or later you should not get this if you have Anti-Virus Software running and it's current. You will get this in Outlook 2003.

for each mail OUTLOOK is giving a message"A program is trying to send an e-mail message on your behalf"

I'm running on Outlook 2007 and have updated Anti Virus Software running and do not have this issue so I can't help you debug the issue.

I'll attach Code that will send Emails to only those Employees that have an Email Address in EBook...if the Employee does not exist in EBook, or if the Employee DOES exist in EBook and does NOT have an Email Address, that Employee WILL NOT receive an Email. Does this work for you?

Regarding this

If its a single workbook with sheets then each employee will have access to the timings of others and the management doesn't want this.If its individual sheet then ppl will see only their data.

Not true. If all Employee Worksheets are in the same workbook, each Employee will receive ONLY their own Worksheet, NOT all Employee Worksheets.

We can make this happen...let me know how you wish to proceed.

PS: You NEED to know...EBook MUST BE MAINTAINED...that's your job...if it's not it won't work as you expect.

[renjithvakkayil]

If with single workbook job can be done then I am more than happy to have that...I can maintain the Ebook...I dont have an antivirus in my house PC and I am running an office 2013...anyway will check from my office if i get that message...John thanks a lot for taking time and solving my queries patiently....

[jaslake]

Hi renjithvakkayil

Far as I know Office 2013 is not yet available to the general public...do you have a Beta Version? You probably need to look at getting anti virus software on your home PC.
You can look at these two workarounds but you really should not need them:

Free ware:
http://www.contextmagic.com/express-...ee-version.htm

See send keys method here:
http://www.rondebruin.nl/mail/prevent.htm

I'll look at this

If with single workbook job can be done

[renjithvakkayil]

Hi John I tried the code from my office and it works perfectly and its more than enough for my current use...So I think I can close the thread as SOLVED....Thanks a lot for the help...

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.