Send worksheet as an attachment to specified recipients via outlook

Hi All,

I've got some codes form http://www.ozgrid.com/VBA/send-email.htm

Below Codes work fine, but just for one recipient and without any message body.

Please Login or Register  to view this content.

Here I would like to get the options to add more Recipients with the message body.

For Example:
To:[email protected]
CC:[email protected];[email protected];[email protected]
Subject: Data Updated on "System Time"
Message Body: This is a system generated mail. Please do not reply.

I tried with below codes for adding more recipients, but did not get the above desired solution.

Please Login or Register  to view this content.

I hope you guys make it easy for me, Thanks in advance!

hi,
try this one and see if it helps you!

Thanks for ur replying, I just changed the file path name in the module, like:
MyFile = "C:\My Documents\Send worksheet as an attachment.xlsm"

But nothing is working fine, before I was getting File Not Found msg, and now getthing a run-time error '70'" Permission Denied.

Please Login or Register  to view this content.

hi,
the code saves the file in a new location,

Please Login or Register  to view this content.

sends it and then

Please Login or Register  to view this content.

it kills it! don't worry about it!
pls follow the steps:
use the email file, you want to send sheet1.
right click any file from yr desktop, click Properties and see "Location", copy it, then go to code and add the Location and file name you want to be sent.
(i.e "C:\users\sunoffice\desktop\sunoffice.xlsm"), sunoffice.xlsm is the new name of yr file
check the inbox and see what you rcvd!

John, now ur codes working fine, plz see below; but sends the entire workbook instead of just one sheet and makes a backup copy of the workbook. If I run the codes for the next time then it asks me to replace the the existing workbook - test1. Kindly improve the cycle.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

hi sunoffice,
so, you are in email file and want to send sheet1.
in code, give a name to your file, let's say test1.xls
so your path will be:

Please Login or Register  to view this content.

test1.xls if you want to be xls or .xlsm with macro.

Plz also look the smallest codes & link in my 1st post of this thread.

HI,

I modified the Function code and test here. I did not receive any errors.

Please Login or Register  to view this content.

Charles now I just edited the path..., and it's working fine.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

...but It sends the entire workbook instead of a single sheet (sheet1), and makes a backup file (workbook)as test1 in the folder directory (which is not useful for me). The codes run successfully and makes ready the email to send, but I want to sent it automatically, and this process should like be in invisible mode so the sender has not to click on the send mail button. I hope u can understand & help me.

Sir, Have u notice about the codes, which I mentioned in my 1st post of this thread.

hi sunoffice,
it sends just sheet1, try to write something in yr sheet2 and then check in yr emai rcvd.
and if you wanna send it without preview just modify:

Please Login or Register  to view this content.

and pay attention at this:

Please Login or Register  to view this content.

you should have the same file!!
hope it helps you!

John,
Just done these above changes, and it's amazing man! It's directly come into my inbox and without any useless backup copy in the folder drive

I wrote in the sheet2 & sheet3, but I see the received attached via mail just has data on the sheet1 and the other sheets are blank.
So can I hope this will not send any vba codes to the recipients, just forward the data on the sheet1??

hi SunOffice,
as far as i know it sends just data! please run some tests!

Thanks mate!! Yeah It's working fine....!!

...and I've seen some short codes, plz see below:

' http://boisgontierjacques.free.fr/pa...te/outlook.htm

Sub envoi_Feuille()
répertoireAppli = ActiveWorkbook.Path ' Penser à Outils/Références Outlook
Sheets("résultats").Copy ' crée un classeur avec la feuille résultats
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs répertoireAppli & "\Resultats.xls"
ActiveWindow.Close
'--- Envoi par mail
Dim olapp As Outlook.Application
Sheets("destinataires").Select
Range("A11").Select
Do While Not IsEmpty(ActiveCell)
Dim msg As MailItem
Set olapp = New Outlook.Application
Set msg = olapp.CreateItem(olMailItem)
msg.To = ActiveCell.Value
msg.Subject = Range("A2").Value
msg.Body = Range("A5").Value & Chr(13) & Chr(13) & Range("A8").Value & Chr(13) & Chr(13)
msg.Attachments.Add Source:=répertoireAppli & "\Resultats.xls"
msg.Send
ActiveCell.Offset(1, 0).Select
Loop
End Sub

can you/ anyone plz translate it into English?

...and below codes are fine as the above.

Please Login or Register  to view this content.