Duplicate Attachments when sending email using VBA

vtobias

New Member
Joined
Nov 13, 2020
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
Hi everyone,

First time posting here.

I am in no means an expert with VBA, I usually go through google and try to mix and match codes.

I'm stumped with the current project I'm working on.
What I'm trying to do is have a vba code wherein it will send the current workbook I'm working on as an attachment and sent it through email.
The file is aimed to be sent to different areas and recipients, so at times I have to select a filter and send the file again using the VBA code that I have, but what it does is it attached the file again, resulting to having 2 attachments (same file)

Here's the macro I use.

VBA Code:
Sub Email_CurrentWorkBook()

    Dim OlApp As Object
    Dim NewMail As Object

    Set OlApp = CreateObject("Outlook.Application")
    Set NewMail = OlApp.CreateItem(0)


    On Error Resume Next
  Sheets("SELECTOR").Select
    ActiveSheet.Range("AK1:AX128").Select
    ActiveWorkbook.EnvelopeVisible = True
    With ActiveSheet.MailEnvelope
      .Item.sentOnBehalfOfName = Range("Q2").Value
      .Item.To = Range("Q3").Value
      .Item.Cc = Range("Q4").Value
      .Item.Subject = Range("Q5").Value
    .Item.Attachments.Add ActiveWorkbook.FullName
    .Item.Send
    End With
    On Error GoTo 0

    Set NewMail = Nothing
    Set OlApp = Nothing
End Sub
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

daverunt

Well-known Member
Joined
Jul 9, 2009
Messages
1,891
Office Version
  1. 2013
Platform
  1. Windows
See if this works. Place before .Item.Attachments.Add

Code:
    .Item.Subject = Range("Q5").Value
            
    Set attch = .Item.Attachments
 
    While attch.Count > 0
        attch.Remove 1
    Wend
   
    .Item.Attachments.Add ActiveWorkbook.FullName
 

Watch MrExcel Video

Forum statistics

Threads
1,127,321
Messages
5,624,000
Members
416,004
Latest member
reitz1

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