SendMail as attachment and not as link

[str8hing]

I currently have the following code to send mail but when it runs it sends the attachment as a link to the original file and not as a standalone workbook. Any ideas?:

ActiveWorkbook.SaveAs FileName:="C:\MyFiles\my new excel file.xls"
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
With myItem
.Recipients.Add "John Q Receiver@aol.com"
.body = "This is the message text"
.Subject = "This is my message subject"
End With
Set myAttachments = myItem.Attachments
myAttachments.Add "C:\MyFiles\my new excel file.xls", _
olByValue, 1, "My file transmission"
myItem.Send
End Sub

 

[staffie007]

This is what I have in a new module with nothing else:

Sub Mailer()
'Mails without security alert
'Need reference to Outlook in the Project References

Dim objol As New Outlook.Application
Dim objmail As MailItem
Dim Pathname

ActiveWorkbook.SaveAs Filename:="C:\MyFiles\my new excel file.xls"
Pathname = ActiveWorkbook.FullName

Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)

With objmail
.To = "John Q Receiver@aol.com" 'enter in here the email address
' .cc = "whoever" 'enter in here the email address
.Subject = "This is my message subject" '& dname
.Body = "This is the message Text" & _
vbCrLf & "This is more message text" & vbCrLf
.NoAging = True
.ReadReceiptRequested = True
.Attachments.Add Pathname 'adds attachment to email
.Display
End With
Set objmail = Nothing
Set objol = Nothing

'Wait for System to catch up and send
Application.Wait (Now + TimeValue("0:00:04"))
Application.SendKeys "%s"
Application.Wait (Now + TimeValue("0:00:04"))

End Sub

 

[MichaelRo]

If the code is failing on the line:

Dim objol As New Outlook.Application

It would suggest you do not have the required Project References.

In the VB Window goto Tools / References

A dialog box will appear - Scroll down until you reach Microsoft Outlook 10.0 Object Library - Check the box next to it - The code should now run.

Depending on your system - You may only have Outlook 9.0 - Check this one instead.

 

[staffie007]

Thank you sooooooo much!!

It works!!

Is there anyway you can go past the Security Alert without the send key command?

 

[MichaelRo]

Glad to have helped.

Not that I'm aware of.

 

[staffie007]

Why would it not work if Outlook was open?

It just leaves the dialog box open.

 

[MichaelRo]

The security alert will appear when another application attempts to send an e-mail.

If you remove the send keys command - The new message would be open and would then need to be sent manually.

 

[staffie007]

Is there any way in an a macro to disable the read receipts that is set in Outlook, without actually going in to Outlook and disabling it manually and then after sending the emails go back and re-enabling it?

 

[MichaelRo]

Change this line:

.ReadReceiptRequested = True

to False:

.ReadReceiptRequested = False

 

[staffie007]

Thank you so much!

Clearly wasn't too wake.

 

[msohail]

I am wanting to paste MichaelRo's code into VB under the programme that runs the reports i.e. I've created a new module and I want to paste this code to it but don't know how?

I don't want to paste it to individual excel files as there are so many.