Macro to attach workbook to email

[Knyte]

Hi

I have a macro which works fine for me and everyone in my office building. However when people outside of the office building attempts it, it returns a Runtime Error 1004.

What I am trying to do is when a user clicks on a 'Send' button, the code behind it will open up outlook and start a new email page. It will also save and attach the workbook.

As mentioned, works perfectly for me but not anyone else outside of my office building. Any thoughts on why this may be?

The code I have used below (I have a feeling it has something to do with it saving to the desktop first. If anyone can provide a code where it just simply attaches the document to the email, would be much appreciated and hopefully will solve my issue):

Sub Send_By_Email()

Dim OlApp As New Outlook.Application
Dim myNameSp As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myExplorer As Outlook.Explorer
Dim NewMail As Outlook.MailItem
Dim OutOpen As Boolean
Dim WB As Workbook
Dim Desktop, book As String

Desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
book = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
book = book & " - Conflict Form.xls"
ThisWorkbook.SaveCopyAs Desktop & book

' Code to check if Outlook is already Open. If it's not, to open a new one
OutOpen = True
Set myExplorer = OlApp.ActiveExplorer
If TypeName(myExplorer) = "Nothing" Then
OutOpen = False
Set myNameSp = OlApp.GetNamespace("MAPI")
Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox)
Set myExplorer = myInbox.GetExplorer
End If

'Create a new mail message item.
Set NewMail = OlApp.CreateItem(olMailItem)
With NewMail
.Display
.Subject = "Conflict of Interest Form - Completed - " & Range("E26") & ": " & Range("E23")
.To = abc@def.com.au
.Attachments.Add Desktop & book
End With

If Not OutOpen Then OlApp.Quit
Set OlApp = Nothing
Set myNameSp = Nothing
Set myInbox = Nothing
Set myExplorer = Nothing
Set NewMail = Nothing
Kill Desktop & book

End Sub

 

[Walky]

Hello Andrew,

Thanks for the quick reply and finding the source of the macro I used initially .

I would need a variation of the code that is suggested:

1) the 1st part of my macro copies and pastes values across the already open Workbook

2) I would simply need to attach it (without copying/saving it) to an open e-mail

3) once the e-mail is sent, I can just close my original Workbook (in read only)

Could you tell me which parts of the macro you posted the link to, can be erased and which need to be modified in order to acheive this?

I tried to take bits and pieces to create what I need, but it didn't work. This macro fetches the saved copy of the file to attach it... while mine is already open, since I'll send it only after consulting it.

Furthermore, the macro you mention apparently leaves the e-mail open only if you choose to display the attachment. I do not need the attachment to be re-opened in order to be able to write a text in the body of the e-mail before sending it.

I hope I'm not asking too much and any other help would be welcom...

Thx,

W.

 

[Andrew Poulsom]

If you have made changes to the workbook you will have to save it before you can attach it to an EMail (the attachment comes from disk). See the second example in the link I posted.

 

[Walky]

Hi Andrew,

I finally decided to try to adapt part 1 of my macro to the example you mentioned and not the other way ... and it works great. I simply have it paste the values before saving the copy.

Furthermore, if the name of the file needs to remain unchanged, you can leave out the parts where "Copy of" and the date are added: there'll be no name conflict since the copy is saved in a temporary folder.

Something I found confusing was the note, it made me think that I had the choice between sending the e-mail immediately (.Send) or opening the file once it was attached (.Display).

But in fact, these choices are for the e-mail itself, so using ".Display" will prepare the e-mail with the attachment and the specified recipients & text, without sending it. Giving you the opportunity to edit/add something and do the final click.

Thanks a million for pointing me back to Ron de Bruin's site.

Regards,

W.