Hi,

I'm trying to use Excel to add an attachment to an Outlook email
that's already been opened in Outlook. I have code that creates a new
email and adds the attachment, but I want to be able to preserve the
email chain in a reply or add the file after I've already written the
body of the email.

Any help is appreciated! Thanks! Here's the code I mentioned that I
already have:

Sub test()
Dim filename As String
Dim result As Long
Dim OutMail As Object
Dim OutApp As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0) ' this is the line
I expect needs to be changed.


With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Emailing: " & ActiveWorkbook.Name
.Body = ""
.Attachments.Add Environ("temp") & "\zip\" &
Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) & ".zip"
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

See if this weblink can shed any light on the issue...

http://www.exceltip.com/st/Control_O...Excel/464.html


Mark Ivey



"Nathan Berton" <(E-Mail Removed)> wrote in message
news:a115ed56-2572-4901-93e8-(E-Mail Removed)...
> Hi,
>
> I'm trying to use Excel to add an attachment to an Outlook email
> that's already been opened in Outlook. I have code that creates a new
> email and adds the attachment, but I want to be able to preserve the
> email chain in a reply or add the file after I've already written the
> body of the email.
>
> Any help is appreciated! Thanks! Here's the code I mentioned that I
> already have:
>
> Sub test()
> Dim filename As String
> Dim result As Long
> Dim OutMail As Object
> Dim OutApp As Object
>
> Set OutApp = CreateObject("Outlook.Application")
> OutApp.Session.Logon
> Set OutMail = OutApp.CreateItem(0) ' this is the line
> I expect needs to be changed.
>
>
> With OutMail
> .To = ""
> .CC = ""
> .BCC = ""
> .Subject = "Emailing: " & ActiveWorkbook.Name
> .Body = ""
> .Attachments.Add Environ("temp") & "\zip\" &
> Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) & ".zip"
> .Display
> End With
> Set OutMail = Nothing
> Set OutApp = Nothing
> End Sub

Why do you need code to attach a file if the email is open and
presumably displayed on your desktop?

Something like this might work. After setting a reference to the
active Inspector, the .Add method of the Attachments Collection
returns an object reference to the newly added attachment.

Dim olcurrentmail As Outlook.MailItem
Dim olattachments As Outlook.Attachments
Dim newattach As Outlook.Attachment

Set olcurrentmail = ActiveInspector.CurrentItem
Set olattachments = olcurrentmail.Attachments

Set newattach = olattachments.Add("C:\filename.xls")

HTH,
JP


On Apr 15, 4:59*pm, Nathan Berton <Nathan.Ber...@gmail.com> wrote:
> Hi,
>
> I'm trying to use Excel to add an attachment to an Outlook email
> that's already been opened in Outlook. *I have code that creates a new
> email and adds the attachment, but I want to be able to preserve the
> email chain in a reply or add the file after I've already written the
> body of the email.
>
> Any help is appreciated! *Thanks! *Here's the code I mentioned that I
> already have:
>
> Sub test()
> * * Dim filename As String
> * * Dim result As Long
> * * Dim OutMail As Object
> * * Dim OutApp As Object
>
> * * Set OutApp = CreateObject("Outlook.Application")
> * * OutApp.Session.Logon
> * * Set OutMail = OutApp.CreateItem(0) * * * * * * ' this is the line
> I expect needs to be changed.
>
> * * With OutMail
> * * * * .To = ""
> * * * * .CC = ""
> * * * * .BCC = ""
> * * * * .Subject = "Emailing: " & ActiveWorkbook.Name
> * * * * .Body = ""
> * * * * .Attachments.Add Environ("temp") & "\zip\" &
> Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) & ".zip"
> * * * * .Display
> * * End With
> * * Set OutMail = Nothing
> * * Set OutApp = Nothing
> End Sub

Thanks a bunch to all 3 of you for your help!

JP--your code works great and does exactly what I need. To answer
your question, the code saves a ton of time versus going through
Outlook interface, making sure I've saved the file I'm working on,
having to close the file down to zip it, and then finding the zip file
and attaching it. I do this 20 to 30 times a day, so it can literally
save me multiple hours in a week.

Mark, thanks for the reference. I'm sure it will come in useful if I
take this further. Steve, thanks for your insights; again, I'm sure
they'll be handy if this macro doesn't solve all my problems.


On Apr 15, 8:26*pm, JP <jp2...@earthlink.net> wrote:
> Why do you need code to attach a file if the email isopenand
> presumably displayed on your desktop?
>
> Something like this might work. After setting a reference to the
> active Inspector, the .Add method of the Attachments Collection
> returns an object reference to the newly added attachment.
>
> Dim olcurrentmail AsOutlook.MailItem
> Dim olattachments AsOutlook.Attachments
> Dim newattach AsOutlook.Attachment
>
> Set olcurrentmail = ActiveInspector.CurrentItem
> Set olattachments = olcurrentmail.Attachments
>
> Set newattach = olattachments.Add("C:\filename.xls")
>
> HTH,
> JP
>
> On Apr 15, 4:59*pm, Nathan Berton <Nathan.Ber...@gmail.com> wrote:
>
>
>
> > Hi,
>
> > I'm trying to use Excel to add an attachment to anOutlookemail
> > that'salreadybeen opened inOutlook. *I have code that creates a new
> > email and adds the attachment, but I want to be able to preserve the
> > email chain in a reply or add the file after I'vealreadywritten the
> > body of the email.
>
> > Any help is appreciated! *Thanks! *Here's the code I mentioned that I
> >alreadyhave:
>
> > Sub test()
> > * * Dim filename As String
> > * * Dim result As Long
> > * * Dim OutMail As Object
> > * * Dim OutApp As Object
>
> > * * Set OutApp = CreateObject("Outlook.Application")
> > * * OutApp.Session.Logon
> > * * Set OutMail = OutApp.CreateItem(0) * * * * * * ' this is the line
> > I expect needs to be changed.
>
> > * * With OutMail
> > * * * * .To = ""
> > * * * * .CC = ""
> > * * * * .BCC = ""
> > * * * * .Subject = "Emailing: " & ActiveWorkbook.Name
> > * * * * .Body = ""
> > * * * * .Attachments.Add Environ("temp") & "\zip\" &
> > Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4) & ".zip"
> > * * * * .Display
> > * * End With
> > * * Set OutMail = Nothing
> > * * Set OutApp = Nothing
> > End Sub- Hide quoted text -
>
> - Show quoted text -

Glad to hear it worked!

Thx,
JP

On Apr 16, 9:00*pm, Nathan Berton <Nathan.Ber...@gmail.com> wrote:
> Thanks a bunch to all 3 of you for your help!
>
> JP--your code works great and does exactly what I need. *To answer
> your question, the code saves a ton of time versus going through
> Outlook interface, making sure I've saved the file I'm working on,
> having to close the file down to zip it, and then finding the zip file
> and attaching it. *I do this 20 to 30 times a day, so it can literally
> save me multiple hours in a week.
>
> Mark, thanks for the reference. *I'm sure it will come in useful if I
> take this further. *Steve, thanks for your insights; again, I'm sure
> they'll be handy if this macro doesn't solve all my problems.
>
> On Apr 15, 8:26*pm, JP <jp2...@earthlink.net> wrote:
>