VBA Macro attaching .xls and .doc to e-mail

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running a VBA macro out of Excel that will distribute dashboards to
people within my company through Outlook and include a standard Word document
that goes to everyone. The standard word document was causing an error after
exactly 100 e-mails were created. I changed some of the code around and now
it only allows 42 e-mails to get created and the error appears. It has
nothing to do with the size of the attachments because they are small. If
I just send the Excel file, everything works fine. The error is as follows:

Run-time error '-2147467259 (80004005)':

Can't create file: SURVEY LETTER.doc. Right clieck the folder you want to
create the fine in, and then click properties..........

Any ideas?
I'm willing to use different code. I'm an Accountant that has become
dangerous with picking-up different pieces of code. Thank you.



Dim emailCnt As Integer

'OutlookRunning = IsOutlookRunning()
If OutlookRunning Then
Set olApp = GetObject(, "Outlook.Application")
Else
Set olApp = CreateObject("Outlook.Application")
End If

'Get e-mail verbage
Windows("Servicemacro.xls").Activate
Sheets("Main").Select
Subject = Cells(5, 4)
Par1 = Cells(6, 4)
Par2 = Cells(7, 4)
Par3 = Cells(8, 4)
Par4 = Cells(9, 4)
doc = Cells(16, 4)

strBody = Par1 & vbNewLine & "" _
& vbNewLine & Par2 & vbNewLine & "" _
& vbNewLine & Par3 & vbNewLine & "" _
& vbNewLine & Par4

'Open file to get e-mail address
Windows("Servicemacro.xls").Activate
Sheets("Emps").Select
RowNo = 4
emailCnt = 0

Do Until IsEmpty(Cells(RowNo, 8))
'Gets IDENTIFICATION DATA
EmpID = Cells(RowNo, 1)
LastName = Cells(RowNo, 2)
If Cells(RowNo, 9) <> "no" Then
Set oldumMailItem = olApp.CreateItem(olMailItem)
With oldumMailItem
.Subject = Subject & LastName
.Body = vbNewLine & strBody & vbNewLine & vbNewLine & vbTab
'.Importance = olImportanceHigh
.Attachments.Add doc
.Attachments.Add "K:\Accting\Survey\Canada\Files\" &
LastName & " " & EmpID & ".xls"
.to = Cells(RowNo, 8)
.Display
emailCnt = emailCnt + 1
If emailCnt Mod 103 = 0 Then Stop
End With
RowNo = RowNo + 1
End If
Loop
End Sub
 
Which statement raises the error? Have you cleaned our your Temp folder lately?
 
Thank you. I cleaned out my temp folder and go the same error.

This morning, I tried the macro on different machines. A desktop allowed 99
e-mails and a thin terminal allowed 100.

The error is coming from the .attachment.add doc line, where doc equals a
file name I have the user enter on the Excel worksheet. I had it hard coded
like the .xls line below, but I tried to get the file to be a little more
user friendly.

If I delete the doc line, the .xls line will work with no problems. If I
delete the .xls line, I get the exact same error allowing the exact same
amount of e-mail messages. Do you have a good macro that will attach files
in Outlook? I really don't care how the code works, I just want it to work.
Thank you.


.Attachments.Add doc
.Attachments.Add "K:\Accting\Survey\Canada\Files\" & LastName &
" " & EmpID & ".xls"
 
Clearing out the Outlook secure temp folder may also be necessary. This article describes how to control the location of that folder -- http://support.microsoft.com/kb/305982/ -- but you can also locate it by opening an attachment and invoking the FIle | Save As menu to see where the opened attachment is stored.
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54
 
Awesome! Thank you. I had 60 files with the same name on my computer. I'll
have to see about the others on the team.
 
Back
Top