+ Reply to Thread
Results 1 to 13 of 13

Automatically attach Excel spreadsheet to Outlook Email with Macro?

  1. #1
    Registered User
    Join Date
    07-18-2006
    Posts
    73

    Automatically attach Excel spreadsheet to Outlook Email with Macro?

    I am at work and made a macro to do some simple automation of some daily reports I have to do. Everyday I have to send this report to one person and CC it to 3 different people. I wanted to include this in the current Macro.

    Is there some way in the code to add this so that it automatically opens Microsoft Outlook and makes this an attachment?

    Thanks,

    ~J

  2. #2
    mhng
    Guest

    RE: Automatically attach Excel spreadsheet to Outlook Email with Macro

    Try this:

    ActiveWorkbook.SendMail _
    Recipients:="[email protected]", _
    Subject:="Subject header" & Format(Date, "dd/mmm/yy")

    "nbaj2k" wrote:

    >
    > I am at work and made a macro to do some simple automation of some daily
    > reports I have to do. Everyday I have to send this report to one person
    > and CC it to 3 different people. I wanted to include this in the
    > current Macro.
    >
    > Is there some way in the code to add this so that it automatically
    > opens Microsoft Outlook and makes this an attachment?
    >
    > Thanks,
    >
    > ~J
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >
    >


  3. #3
    Registered User
    Join Date
    04-05-2006
    Posts
    5

    Further Problem.

    At risk of hijacking the thread:

    Is there anyway to tailor Application.sendmail so that it sends
    the current worksheet as the main body of the mail. Excel does this quite readily when sending manually so i assume it is possible. The only code or description I can find online is the Ron de Bruin code, or a variant thereof. This is a nifty little piece of code but i am just sending some summary data concatenated into a letter form so i would rather that it appeared as such right off the the bat.

    Hello and goodbye from London.



    Quote Originally Posted by mhng
    Try this:

    ActiveWorkbook.SendMail _
    Recipients:="[email protected]", _
    Subject:="Subject header" & Format(Date, "dd/mmm/yy")

    "nbaj2k" wrote:

    >
    > I am at work and made a macro to do some simple automation of some daily
    > reports I have to do. Everyday I have to send this report to one person
    > and CC it to 3 different people. I wanted to include this in the
    > current Macro.
    >
    > Is there some way in the code to add this so that it automatically
    > opens Microsoft Outlook and makes this an attachment?
    >
    > Thanks,
    >
    > ~J
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >
    >

  4. #4
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi Aoife

    With OE you can do this
    http://www.rondebruin.nl/mail/oebody.htm

    But I like CDO
    http://www.rondebruin.nl/cdo.htm

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "Aoife" <[email protected]> wrote in message
    news:[email protected]...
    >
    > At risk of hijacking the thread:
    >
    > Is there anyway to tailor Application.sendmail so that it sends
    > the current worksheet as the main body of the mail. Excel does this
    > quite readily when sending manually so i assume it is possible. The
    > only code or description I can find online is the Ron de Bruin code, or
    > a variant thereof. This is a nifty little piece of code but i am just
    > sending some summary data concatenated into a letter form so i would
    > rather that it appeared as such right off the the bat.
    >
    > Hello and goodbye from London.
    >
    >
    >
    > mhng Wrote:
    >> Try this:
    >>
    >> ActiveWorkbook.SendMail _
    >> Recipients:="[email protected]", _
    >> Subject:="Subject header" & Format(Date, "dd/mmm/yy")
    >>
    >> "nbaj2k" wrote:
    >>
    >> >
    >> > I am at work and made a macro to do some simple automation of some

    >> daily
    >> > reports I have to do. Everyday I have to send this report to one

    >> person
    >> > and CC it to 3 different people. I wanted to include this in the
    >> > current Macro.
    >> >
    >> > Is there some way in the code to add this so that it automatically
    >> > opens Microsoft Outlook and makes this an attachment?
    >> >
    >> > Thanks,
    >> >
    >> > ~J
    >> >
    >> >
    >> > --
    >> > nbaj2k
    >> >

    >> ------------------------------------------------------------------------
    >> > nbaj2k's Profile:

    >> http://www.excelforum.com/member.php...o&userid=36480
    >> > View this thread:

    >> http://www.excelforum.com/showthread...hreadid=562416
    >> >
    >> >

    >
    >
    > --
    > Aoife
    > ------------------------------------------------------------------------
    > Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




  5. #5
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi nbaj2k

    You must use the Outlook object model if you want to use CC.
    Not possible with SendMail

    See my site for example code (click also on the Tip link on every example page)
    http://www.rondebruin.nl/sendmail.htm


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "nbaj2k" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I am at work and made a macro to do some simple automation of some daily
    > reports I have to do. Everyday I have to send this report to one person
    > and CC it to 3 different people. I wanted to include this in the
    > current Macro.
    >
    > Is there some way in the code to add this so that it automatically
    > opens Microsoft Outlook and makes this an attachment?
    >
    > Thanks,
    >
    > ~J
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




  6. #6
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi Aoife

    With OE you can do this
    http://www.rondebruin.nl/mail/oebody.htm

    But I like CDO
    http://www.rondebruin.nl/cdo.htm

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "Aoife" <[email protected]> wrote in message
    news:[email protected]...
    >
    > At risk of hijacking the thread:
    >
    > Is there anyway to tailor Application.sendmail so that it sends
    > the current worksheet as the main body of the mail. Excel does this
    > quite readily when sending manually so i assume it is possible. The
    > only code or description I can find online is the Ron de Bruin code, or
    > a variant thereof. This is a nifty little piece of code but i am just
    > sending some summary data concatenated into a letter form so i would
    > rather that it appeared as such right off the the bat.
    >
    > Hello and goodbye from London.
    >
    >
    >
    > mhng Wrote:
    >> Try this:
    >>
    >> ActiveWorkbook.SendMail _
    >> Recipients:="[email protected]", _
    >> Subject:="Subject header" & Format(Date, "dd/mmm/yy")
    >>
    >> "nbaj2k" wrote:
    >>
    >> >
    >> > I am at work and made a macro to do some simple automation of some

    >> daily
    >> > reports I have to do. Everyday I have to send this report to one

    >> person
    >> > and CC it to 3 different people. I wanted to include this in the
    >> > current Macro.
    >> >
    >> > Is there some way in the code to add this so that it automatically
    >> > opens Microsoft Outlook and makes this an attachment?
    >> >
    >> > Thanks,
    >> >
    >> > ~J
    >> >
    >> >
    >> > --
    >> > nbaj2k
    >> >

    >> ------------------------------------------------------------------------
    >> > nbaj2k's Profile:

    >> http://www.excelforum.com/member.php...o&userid=36480
    >> > View this thread:

    >> http://www.excelforum.com/showthread...hreadid=562416
    >> >
    >> >

    >
    >
    > --
    > Aoife
    > ------------------------------------------------------------------------
    > Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




  7. #7
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi nbaj2k

    You must use the Outlook object model if you want to use CC.
    Not possible with SendMail

    See my site for example code (click also on the Tip link on every example page)
    http://www.rondebruin.nl/sendmail.htm


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "nbaj2k" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I am at work and made a macro to do some simple automation of some daily
    > reports I have to do. Everyday I have to send this report to one person
    > and CC it to 3 different people. I wanted to include this in the
    > current Macro.
    >
    > Is there some way in the code to add this so that it automatically
    > opens Microsoft Outlook and makes this an attachment?
    >
    > Thanks,
    >
    > ~J
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




  8. #8
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi Aoife

    With OE you can do this
    http://www.rondebruin.nl/mail/oebody.htm

    But I like CDO
    http://www.rondebruin.nl/cdo.htm

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "Aoife" <[email protected]> wrote in message
    news:[email protected]...
    >
    > At risk of hijacking the thread:
    >
    > Is there anyway to tailor Application.sendmail so that it sends
    > the current worksheet as the main body of the mail. Excel does this
    > quite readily when sending manually so i assume it is possible. The
    > only code or description I can find online is the Ron de Bruin code, or
    > a variant thereof. This is a nifty little piece of code but i am just
    > sending some summary data concatenated into a letter form so i would
    > rather that it appeared as such right off the the bat.
    >
    > Hello and goodbye from London.
    >
    >
    >
    > mhng Wrote:
    >> Try this:
    >>
    >> ActiveWorkbook.SendMail _
    >> Recipients:="[email protected]", _
    >> Subject:="Subject header" & Format(Date, "dd/mmm/yy")
    >>
    >> "nbaj2k" wrote:
    >>
    >> >
    >> > I am at work and made a macro to do some simple automation of some

    >> daily
    >> > reports I have to do. Everyday I have to send this report to one

    >> person
    >> > and CC it to 3 different people. I wanted to include this in the
    >> > current Macro.
    >> >
    >> > Is there some way in the code to add this so that it automatically
    >> > opens Microsoft Outlook and makes this an attachment?
    >> >
    >> > Thanks,
    >> >
    >> > ~J
    >> >
    >> >
    >> > --
    >> > nbaj2k
    >> >

    >> ------------------------------------------------------------------------
    >> > nbaj2k's Profile:

    >> http://www.excelforum.com/member.php...o&userid=36480
    >> > View this thread:

    >> http://www.excelforum.com/showthread...hreadid=562416
    >> >
    >> >

    >
    >
    > --
    > Aoife
    > ------------------------------------------------------------------------
    > Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




  9. #9
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi nbaj2k

    You must use the Outlook object model if you want to use CC.
    Not possible with SendMail

    See my site for example code (click also on the Tip link on every example page)
    http://www.rondebruin.nl/sendmail.htm


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "nbaj2k" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I am at work and made a macro to do some simple automation of some daily
    > reports I have to do. Everyday I have to send this report to one person
    > and CC it to 3 different people. I wanted to include this in the
    > current Macro.
    >
    > Is there some way in the code to add this so that it automatically
    > opens Microsoft Outlook and makes this an attachment?
    >
    > Thanks,
    >
    > ~J
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




  10. #10
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi Aoife

    With OE you can do this
    http://www.rondebruin.nl/mail/oebody.htm

    But I like CDO
    http://www.rondebruin.nl/cdo.htm

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "Aoife" <[email protected]> wrote in message
    news:[email protected]...
    >
    > At risk of hijacking the thread:
    >
    > Is there anyway to tailor Application.sendmail so that it sends
    > the current worksheet as the main body of the mail. Excel does this
    > quite readily when sending manually so i assume it is possible. The
    > only code or description I can find online is the Ron de Bruin code, or
    > a variant thereof. This is a nifty little piece of code but i am just
    > sending some summary data concatenated into a letter form so i would
    > rather that it appeared as such right off the the bat.
    >
    > Hello and goodbye from London.
    >
    >
    >
    > mhng Wrote:
    >> Try this:
    >>
    >> ActiveWorkbook.SendMail _
    >> Recipients:="[email protected]", _
    >> Subject:="Subject header" & Format(Date, "dd/mmm/yy")
    >>
    >> "nbaj2k" wrote:
    >>
    >> >
    >> > I am at work and made a macro to do some simple automation of some

    >> daily
    >> > reports I have to do. Everyday I have to send this report to one

    >> person
    >> > and CC it to 3 different people. I wanted to include this in the
    >> > current Macro.
    >> >
    >> > Is there some way in the code to add this so that it automatically
    >> > opens Microsoft Outlook and makes this an attachment?
    >> >
    >> > Thanks,
    >> >
    >> > ~J
    >> >
    >> >
    >> > --
    >> > nbaj2k
    >> >

    >> ------------------------------------------------------------------------
    >> > nbaj2k's Profile:

    >> http://www.excelforum.com/member.php...o&userid=36480
    >> > View this thread:

    >> http://www.excelforum.com/showthread...hreadid=562416
    >> >
    >> >

    >
    >
    > --
    > Aoife
    > ------------------------------------------------------------------------
    > Aoife's Profile: http://www.excelforum.com/member.php...o&userid=33191
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




  11. #11
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi nbaj2k

    You must use the Outlook object model if you want to use CC.
    Not possible with SendMail

    See my site for example code (click also on the Tip link on every example page)
    http://www.rondebruin.nl/sendmail.htm


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "nbaj2k" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I am at work and made a macro to do some simple automation of some daily
    > reports I have to do. Everyday I have to send this report to one person
    > and CC it to 3 different people. I wanted to include this in the
    > current Macro.
    >
    > Is there some way in the code to add this so that it automatically
    > opens Microsoft Outlook and makes this an attachment?
    >
    > Thanks,
    >
    > ~J
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




  12. #12
    Registered User
    Join Date
    07-18-2006
    Posts
    73

    I've got something started, but its still not automatic....

    I can't get to that site from here at work.

    I have looked at code around and pieced some things together this is what I have so far, I still have no idea how to attach the current file I'm in automatically.

    Any other ideas?

    The bolded line is the one I somehow want to change to make it automatically pick the sheet I'm in to attach so I don't have to pick it.

    Thanks,

    ~J

    Sub Email()
    Dim myattachment
    Dim olNs As Object
    Dim olMailItem
    Dim olMail As Object
    Dim olApp As Object
    Dim ATTACH1 As String


    ATTACH1 = Application.GetOpenFilename("Text Files (*.*), *.*")
    Set olApp = CreateObject("Outlook.Application")
    Set olNs = olApp.GetNamespace("MAPI")
    olNs.Logon
    Set olMail = olApp.CreateItem(olMailItem)
    olMail.Display

    olMail.To = "(ADDRESS)"
    olMail.CC = "(ADDRESS)"
    'olMail.BCC =
    olMail.Subject = "Daily Report for: " & Format(Date - 1, "d-mmm-yy")
    olMail.Body = vbCr & vbCr & "(TEXT HERE)" & Format(Date, "d-mmm-yy") & "
    Set myattachment = olMail.Attachments
    myattachment.Add ATTACH1
    Stop
    'olMail.Send
    olNs.Logoff
    Set olNs = Nothing
    Set olMail = Nothing
    Set olApp = Nothing
    End Sub

  13. #13
    Ron de Bruin
    Guest

    Re: Automatically attach Excel spreadsheet to Outlook Email with Macro?

    Hi nbaj2k

    >I can't get to that site from here at work.


    Why they block NL sites ?
    Can you ask that for me

    Try this

    This example send the last saved version of the Activeworkbook.
    You must add a reference to the Microsoft outlook Library.

    1) Go to the VBA editor, Alt -F11
    2) Tools>References in the Menu bar
    3) Place a Checkmark before Microsoft Outlook ? Object Library
    ? is the Excel version number


    Sub Mail_workbook_Outlook()
    'This example send the last saved version of the Activeworkbook
    'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "This is the Subject line"
    .Body = "Hi there"
    .Attachments.Add ActiveWorkbook.FullName
    'You can add other files also like this
    '.Attachments.Add ("C:\test.txt")
    .Send 'or use .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    End Sub

    You can use this for more addresses
    ..To = "[email protected];[email protected]"



    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "nbaj2k" <[email protected]> wrote in message
    news:[email protected]...
    >
    > I can't get to that site from here at work.
    >
    > I have looked at code around and pieced some things together this is
    > what I have so far, I still have no idea how to attach the current file
    > I'm in automatically.
    >
    > Any other ideas?
    >
    > The bolded line is the one I somehow want to change to make it
    > automatically pick the sheet I'm in to attach so I don't have to pick
    > it.
    >
    > Thanks,
    >
    > ~J
    >
    > Sub Email()
    > Dim myattachment
    > Dim olNs As Object
    > Dim olMailItem
    > Dim olMail As Object
    > Dim olApp As Object
    > Dim ATTACH1 As String
    >
    >
    > ATTACH1 = APPLICATION.GETOPENFILENAME(\"TEXT FILES (*.*), *.*\")
    > Set olApp = CreateObject("Outlook.Application")
    > Set olNs = olApp.GetNamespace("MAPI")
    > olNs.Logon
    > Set olMail = olApp.CreateItem(olMailItem)
    > olMail.Display
    >
    > olMail.To = "(ADDRESS)"
    > olMail.CC = "(ADDRESS)"
    > 'olMail.BCC =
    > olMail.Subject = "Daily Report for: " & Format(Date - 1, "d-mmm-yy")
    > olMail.Body = vbCr & vbCr & "(TEXT HERE)" & Format(Date, "d-mmm-yy") &
    > "
    > Set myattachment = olMail.Attachments
    > myattachment.Add ATTACH1
    > Stop
    > 'olMail.Send
    > olNs.Logoff
    > Set olNs = Nothing
    > Set olMail = Nothing
    > Set olApp = Nothing
    > End Sub
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=562416
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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