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

 

[guamlet]

Does it have to be 4 seconds?

This works pretty **** well! Kudos to you MichaelRo! Can you make this work faster by changing the delay time to less than 4 seconds, or is that the limit? I also noticed it only works when you use .Display instead of .Send. Any way to make it work with .Send?

Guamlet

 

[MichaelRo]

You could change the delay time - I found that when I changed it to 2 seconds it started failing on some messages so just left it as it was.

 

[ejs7597]

User-Defined type not defined error on the following line of code:

Dim objol As New Outlook.Application

This worked last week when I wrote the macro, but now it is failing on all of the machines.
The Microsoft Office 11.0 Object Library is still checked in the references.

 

[ejs7597]

I figured out that my problem was that I clicked on the wrong reference. I chose Office 11 instead of Outlook 11. Works fine when you choose the correct reference. Imagine that.

 

[dmwilson38550m]

Hi,

I have a list of email addresses on an Excel document. When I try to send an email with an attachment (using a macro programme there are normally 10 emails each with an attachment sent one after another) I encounter the following warning message before each email will send:

A programme is automatically trying to send an email on your behalf.
Do you want to allow this?

If this is unexpected, it may be a virus and you should choose "no".

Does anyone know how to overcome the security - I am using MS Windows OUTLOOK 2003. I think it has something to do with inserting SEND KEYS...(but I don't know where to insert these)

I would appreciate any help at all.....Many thanks
The code I am using so far is...

Sub emailgood()
'
' emailgood Macro
' Macro recorded 21/08/2007 by david.wilson
'
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range

With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set sh = Sheets("Sheet1")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)


Set rng = sh.Cells(cell.Row, 1).Range("C1:z1")

If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = cell.Value
.Subject = "AGED DEBTORS week ending Friday 5th November 2010 : Within Terms vs Overdue Invoices"
.Body = "This is an automated email message:" & vbNewLine & _
"" & vbNewLine & _
"Hello," & vbNewLine & _
"" & vbNewLine & _
"This email is automatically sent XYZ happens" & vbNewLine & _
"" & vbNewLine & _
"The email subject and the attached file have the same unique date and time for ease of identification." & vbNewLine & _
"You may want to create a Folder on your desktop in which to store these attachments." & vbNewLine & _
"Alternatively you can create a Rule within Outlook to save these emails to a Folder within your Inbox." & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
"If you require anymore information either within this email or in the attached file," & vbNewLine & _
"then contact the sender of the file." & vbNewLine & _
"" & vbNewLine & _
"End of message"
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell

.Send

End With

Set OutMail = Nothing
End If
Next cell

Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With



'
End Sub

 

[ejs7597]

It's been a while since I messed with this, but I think I had to change the way I was sending the email to get past that warning. Here is the code I use in one of my files that doesn't cause the warning.


Sub EmailOrder()
'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)

strbody = "An order has been ADDED to Picklist by the Loaders" & ActiveWorkbook.FullName & vbNewLine & _
"Order Number " & UserForm2.TextBox1.Value & vbNewLine & _
"" & vbNewLine & _
"" & vbNewLine & _
""

With objmail
.To = "cindyj; richarde" 'enter in here the email address
' .cc = "whoever" 'enter in here the email address
.Subject = "An Order has been added to the picklist" '& dname
.Body = strbody
.NoAging = True
.ReadReceiptRequested = False
'.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:1"))
Application.SendKeys "%s"
'Application.Wait (Now + TimeValue("0:00:01"))

End Sub

 

[dmwilson38550m]

HI,

I have tried making a few modifications to your script. I have all email recipients details on Sheet1

Column A Column B Column C
Name Recipient email Address File name (.xls) and location/drive

I want the macro to look along row 1, pick up the recipients email address (column B) and attach the appropriate file (column C) to the email.

Then I want the macro to look along row 2, and repeat the process, then row 3 and so and so forth.

My problem is that I need to select YES every time the email generates to send it - is there a way I automatically select YES within my existing script.

Thanks,
D

 

[ejs7597]

Check out this thread.
http://www.mrexcel.com/forum/showthread.php?t=224671&highlight=Mails+security+alert

 

[dmwilson38550m]

Thanks. This directed me to a link where I downloaded a trial version of ClickYesPro2010. It runs in the background and means I don't need to click YES everytime outlook tries to send an email.

Many thanks,
David