How to clear repeating attachments

[kengi]

Hi, I'm new EXCEL VBA Users and want to ask about problem of using EXCEL VBA for sending email.

I read some article and write the following VBA script in sheet 1 for sending email with attachment:-

******
Sub mail()
For i = 2 To 6
Dim env As MsoEnvelope
Set ws = Parent.Worksheets("Sheet3")
ws.Parent.EnvelopeVisible = True
Set env = ws.MailEnvelope
env.Introduction = Cells(i, 5).Value
env.Item.to = Cells(i, 2).Value
env.Item.CC = ""
env.Item.Subject = Cells(i, 3).Value
env.Item.attachments.Add Cells(i, 4).Value
env.Item.Send
Next i
End Sub
******

Content in SHEET 1

********
Name email adress subject attachment path Attn
AAA AAA@XXXmail.com Try Email D:\VBA\fax-1-HH.pdf Mr.A
BBB BBB@XXXmail.com Try Email D:\VBA\fax-1-CSC.pdf Mr.B
CCC CCC@XXXmail.com Try Email D:\VBA\fax-1-GCL.pdf Mr.C
DDD DDD@XXXmail.com Try Email D:\VBA\fax-1-HC.pdf Mr.D
EEE EEE@XXXmail.com Try Email D:\VBA\fax-1-PY.pdf Mr.E
*********

emails are sent out and then received with correct attn.
But the attachments are repeated and accumulated.
1st email with fax-1-HH.pdf
2nd email with fax-1-HH.pdf and fax-1-CSC.pdf
3rd email with fax-1-HH.pdf, fax-1-CSC.pdf and fax-1-GCL.pdf
.......
if the script run in second time
1st email consists 6 attachments
2nd email consists 7 attachments
........

Please advice how can I enclose 1 attachment in each email.

Thanks.


kengi

[MaximS]

i don't know if that will work but you can always try

change this:

env.Item.Subject = Cells(i, 3).Value
env.Item.attachments.Add Cells(i, 4).Value
env.Item.Send


for that:

env.Item.Subject = Cells(i, 3).Value
env.Item.attachments.Add Cells(i, 4).Value
env.Item.Send
Set env.Item.attachments = Nothing

[kengi]

i don't know if that will work but you can always try

change this:

Set env.Item.attachments = Nothing

Thnaks MaximS

I'd tried but it doesn't work.
"Set env.Item.attachments = Nothing" at the end cause error
I put it at the begining. No error but no improvement.

Is it the problem of my PC or outlook?

[BrianMH]

Try this instead

env.Item.Subject = Cells(i, 3).Value
env.Item.attachments.Add Cells(i, 4).Value
env.Item.Send
set env = nothing

You need to clear out the env so a whole new item can be created. Otherwise you are using the same item and just changing the properties. Each time you envi.item.attachment.add you are adding a new attachement to the current item.

[kengi]

Thanks BrianMH,

"Set env = Nothing" is also not working.
The email accumulated over twenty attachments.

[BrianMH]

I'm sure there is a better solution. what do ou want it to do. this seems to be actually sending a sheet and the attachment

[BrianMH]

Ok now that I am home (I was using my phone before) I can give you a better example and this does work.

Option Explicit

Sub mail()
'requires reference to outlook to be created
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim CurrFile As String
Dim i As Integer
Dim wsData As Sheet1
Set wsData = Sheets("sheet1")

Set olApp = New Outlook.Application


For i = 2 To 6
Set olMail = olApp.CreateItem(olMailItem)
With olMail
olMail.To = wsData.Cells(i, 1).Value
olMail.Subject = wsData.Cells(i, 2).Value
olMail.Attachments.Add (wsData.Cells(i, 3).Value)
olMail.Body = wsData.Cells(i, 4).Value
olMail.Send
End With
Set olMail = Nothing

Next
Set olApp = Nothing
End Sub
As commented in the code you need to set a reference to outlook.
This is done in the Tools>references menu.

I would actually consider changing the olmail.send to olmail.display then you can see it before sending and simply press the send button

I would also consider doing a while statement to keep offsetting the first row and while this is not empty continuing. Then you can have as many rows of data as you want and continue sending mails out. Of course with lots of mails you wouldn't want to display each one as this would clog up your screen and memory.

I'm no expert I've learned mainly from looking at help files but hope this helps.

[kengi]

Thank you very much BrianMH

That's great. It works. The problem is solved. It can be used now.

BTW. Is it possible to send a "Sheet" instead of the TXT Body?
The Format of a "Sheet" is better.

[BrianMH]

I'm not sure how you would do that. However if your familiar with some simple html you could put all your html into that cell (of course there are size limits but not sure how big your mail is. Then use this code. I also noticed I used a with statement but still typed out the olmail each time so I fixed that. You could even spread out the html over several cells then join them in the code. This would let you add specific information to suit each reciever.

Sub mail()
'requires reference to outlook to be created
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim CurrFile As String
Dim i As Integer
Dim wsData As Sheet1
Set wsData = Sheets("sheet1")

Set olApp = New Outlook.Application


For i = 2 To 6
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = wsData.Cells(i, 1).Value
.Subject = wsData.Cells(i, 2).Value
.Attachments.Add (wsData.Cells(i, 3).Value)
.BodyFormat = olFormatHTML
.HTMLBody = wsData.Cells(i, 4).Value
.display
End With
Set olMail = Nothing

Next
Set olApp = Nothing
End Sub