+ Reply to Thread
Results 1 to 15 of 15

Send worksheet as an attachment to specified recipients via outlook

  1. #1
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Question 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:1is2@gmaaal.com
    CC:1is3@gmaaal.com;1is4@gmaaal.com;2is20@hotttmail.com
    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!
    Last edited by SunOffice; 04-10-2011 at 07:55 AM.
    Excelforum is Completely Awesome! True learning with Live Examples & Best Techniques!!

  2. #2
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    1,964

    Re: Send worksheet as an attachment to specified recipients via outlook

    hi,
    try this one and see if it helps you!
    Attached Files Attached Files
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  3. #3
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Send worksheet as an attachment to specified recipients via outlook

    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.
    Last edited by SunOffice; 04-10-2011 at 09:09 AM.

  4. #4
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    1,964

    Re: Send worksheet as an attachment to specified recipients via outlook

    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!
    Last edited by john55; 04-10-2011 at 12:35 PM.

  5. #5
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Send worksheet as an attachment to specified recipients via outlook

    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.
    Last edited by SunOffice; 04-10-2011 at 07:41 PM.

  6. #6
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    1,964

    Re: Send worksheet as an attachment to specified recipients via outlook

    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.

  7. #7
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Send worksheet as an attachment to specified recipients via outlook

    Plz also look the smallest codes & link in my 1st post of this thread.
    Last edited by SunOffice; 04-10-2011 at 07:46 PM.

  8. #8
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: Send worksheet as an attachment to specified recipients via outlook

    HI,

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

    Please Login or Register  to view this content.
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  9. #9
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Send worksheet as an attachment to specified recipients via outlook

    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.
    Last edited by SunOffice; 04-10-2011 at 07:29 PM.

  10. #10
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    1,964

    Re: Send worksheet as an attachment to specified recipients via outlook

    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!
    Last edited by john55; 04-11-2011 at 02:41 AM.

  11. #11
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Send worksheet as an attachment to specified recipients via outlook

    Maybe have a look at Ron de bruin's page.
    A complete solution for sending workbooks, sheets, values, etc is provided as an add in

  12. #12
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Send worksheet as an attachment to specified recipients via outlook

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

  13. #13
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    1,964

    Re: Send worksheet as an attachment to specified recipients via outlook

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

  14. #14
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Send worksheet as an attachment to specified recipients via outlook

    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?
    Last edited by SunOffice; 05-23-2011 at 04:43 AM.

  15. #15
    Forum Contributor
    Join Date
    08-12-2010
    Location
    Excel World
    MS-Off Ver
    Excel 2010 & 2007
    Posts
    210

    Re: Send worksheet as an attachment to specified recipients via outlook

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

    Please Login or Register  to view this content.
    Last edited by SunOffice; 06-19-2011 at 05:41 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1