Gmail incl too many files as attachment

kjaer

New Member
Joined
Jul 24, 2013
Messages
8
Hi guys

I've created an excel macro that basically sends an email with an attachment included. I'm using gmail as mailsystem.

Basically everything works fine, except for that fact that my attachment from first email generated will be included in my second email generated, both attachments from second email will be included in my third email etc. In other words every email contains every attachment included in previous emails.

What am I doing wrong :)?

Thanks,
Kjaer

This is (most of) my code:

Sub gmail ()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Dim myCliName As String
Dim myCliMail As String
Dim myCliFile As String


Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")


iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "myemail@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mypassword"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Update
End With


strbody = "Hello" & vbNewLine & vbNewLine & _
"bla bla bla" & vbNewLine & _
"best regards"

' Identify recipient data
myWS.Select
Dim myCell As Range, myRng As Range
Set myRng = Range("A2", Range("A2").End(xlDown))
myMailCount = 0

For Each myCell In myRng
myCliName = myCell.Value
myCliMail = myCell.Offset(0, 1).Value
myCliFile = myCell.Offset(0, 3).Value

'Sending mail
With iMsg
Set .Configuration = iConf
.To = myCliMail
.CC = ""
.BCC = ""
.From = """me"" <myemail@gmail.com>"
.Subject = myCliName
.TextBody = strbody
.AddAttachment myCliFile
.Send
End With

myMailCount = myMailCount + 1
myCliName = Empty
myCliMail = Empty
myCliFile = Empty

Next myCell


MsgBox myMailCount & " emails have been sent", vbInformation, "Emails sent"

End Sub</myemail@gmail.com>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,450
Try this...

Code:
[color=green]'Sending mail[/color]
[color=darkblue]With[/color] iMsg
    [color=darkblue]Set[/color] .Configuration = iConf
    .To = myCliMail
    .CC = ""
    .BCC = ""
    .From = """me"" "
    .Subject = myCliName
    .TextBody = strbody
   [B] .Attachments.DeleteAll[/B]
    .AddAttachment myCliFile
    .Send
[color=darkblue]End[/color] [color=darkblue]With[/color]
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,526
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top