My email vba macro does not work

Jyggalag

Jyggalag

Active Member
Joined
Mar 8, 2021
Messages
422
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi all,

I have the current VBA code:

VBA Code: 
Option Explicit

Private Const FilePath As String = "S:\File Path anonymous\"
Sub send_email_complete()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Long
    Dim ws As Worksheet
    Dim col As New Collection, itm As Variant
    Dim ToAddress As String, CCAddress As String, EmailSubject As String
   
    '~~> Change this to the relevant worksheet
    '~~> that has the emails (right now Search Export has it)
    Set ws = ThisWorkbook.Sheets("Search Export")

    Set OutApp = CreateObject("Outlook.Application")
    
    Dim BodyText As String
    BodyText = ws.Range("G3") & "<BR>" & "<BR>" & _
         ws.Range("G5") & "<BR>" & "<BR>" & _
         ws.Range("G10") & "<BR>" & "<BR>" & _
         ws.Range("G14") & "<BR>" & "<BR>" & _
         ws.Range("G17") & "<BR>" & "<BR>" & _
         ws.Range("G20") & "<BR>" & _
         "<b>" ws.Range("G21") & & "</b><br>" & "<BR>" & _
         ws.Range("G22") & "<BR>" & "<BR>" & _
         ws.Range("G24") & "<BR>" & "<BR>" & _
         ws.Range("G26") & "<BR>" & "<BR>" & _
         ws.Range("G28") & "<BR>" & "<BR>" & _
         ws.Range("G30") & "<BR>" & "<BR>" & _
         ws.Range("G32") & "<BR>" & "<BR>" & _
         ws.Range("G34") & "<BR>" & "<BR>" & _
         ws.Range("G36") & "<BR>" & "<BR>" & _
         ws.Range("G38") & "<BR>" & "<BR>" & _
   Dim AttachmentName As String
   AttachmentName = FilePath & ws.Cells(2, 6).Value2
         
    For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      '~~> Constructing addresses and subject
      
      ToAddress = ws.Cells(i, 2).Value2
                  
      CCAddress = ws.Cells(i, 5).Value2
                   
      EmailSubject = ws.Cells(i, 1).Value2
      '~~> This creates a new email (so we can send out multiple emails)
      Set OutMail = OutApp.CreateItem(0)

      With OutMail
         .To = ToAddress
         .CC = CCAddress
         .Subject = EmailSubject
         .HTMLBody = BodyText
         .Attachments.Add AttachmentName
         
         .Display
      
      End With
   Next i

End Sub

However, when I try to run the code, it highlights my ws.range in red and says my code does not work.

Anybody know why?

Kind regards,
Jyggalag
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
That usually means it can't find a sheet with that name, does the workbook have a "Search Export" worksheet tab? What's the tab name on the sheet you want to pull data from?
 
Upvote 0
Jyggalag said:
Hi all,

I have the current VBA code:

VBA Code: 
Option Explicit

Private Const FilePath As String = "S:\File Path anonymous\"
Sub send_email_complete()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Long
    Dim ws As Worksheet
    Dim col As New Collection, itm As Variant
    Dim ToAddress As String, CCAddress As String, EmailSubject As String
  
    '~~> Change this to the relevant worksheet
    '~~> that has the emails (right now Search Export has it)
    Set ws = ThisWorkbook.Sheets("Search Export")

    Set OutApp = CreateObject("Outlook.Application")
   
    Dim BodyText As String
    BodyText = ws.Range("G3") & "<BR>" & "<BR>" & _
         ws.Range("G5") & "<BR>" & "<BR>" & _
         ws.Range("G10") & "<BR>" & "<BR>" & _
         ws.Range("G14") & "<BR>" & "<BR>" & _
         ws.Range("G17") & "<BR>" & "<BR>" & _
         ws.Range("G20") & "<BR>" & _
         "<b>" ws.Range("G21") & & "</b><br>" & "<BR>" & _
         ws.Range("G22") & "<BR>" & "<BR>" & _
         ws.Range("G24") & "<BR>" & "<BR>" & _
         ws.Range("G26") & "<BR>" & "<BR>" & _
         ws.Range("G28") & "<BR>" & "<BR>" & _
         ws.Range("G30") & "<BR>" & "<BR>" & _
         ws.Range("G32") & "<BR>" & "<BR>" & _
         ws.Range("G34") & "<BR>" & "<BR>" & _
         ws.Range("G36") & "<BR>" & "<BR>" & _
         ws.Range("G38") & "<BR>" & "<BR>" & _
   Dim AttachmentName As String
   AttachmentName = FilePath & ws.Cells(2, 6).Value2
        
    For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      '~~> Constructing addresses and subject
     
      ToAddress = ws.Cells(i, 2).Value2
                 
      CCAddress = ws.Cells(i, 5).Value2
                  
      EmailSubject = ws.Cells(i, 1).Value2
      '~~> This creates a new email (so we can send out multiple emails)
      Set OutMail = OutApp.CreateItem(0)

      With OutMail
         .To = ToAddress
         .CC = CCAddress
         .Subject = EmailSubject
         .HTMLBody = BodyText
         .Attachments.Add AttachmentName
        
         .Display
     
      End With
   Next i

End Sub

However, when I try to run the code, it highlights my ws.range in red and says my code does not work.

Anybody know why?

Kind regards,
Jyggalag
Click to expand...
It highlights this part btw:

1656059047461.png
 
Upvote 0
portews said:
That usually means it can't find a sheet with that name, does the workbook have a "Search Export" worksheet tab? What's the tab name on the sheet you want to pull data from?
Click to expand...
The sheet is called Search Export yes.

I don't know what you mean by the second question?

I have btw edited the code a bit so it looks like this now (but still gives the same error!) :(

VBA Code: 
Option Explicit

Private Const FilePath As String = "S:\PRIVATE PATH\"
Sub send_email_complete()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim i As Long
    Dim ws As Worksheet
    Dim col As New Collection, itm As Variant
    Dim ToAddress As String, CCAddress As String, EmailSubject As String
   
    '~~> Change this to the relevant worksheet
    '~~> that has the emails (right now Search Export has it)
    Set ws = ThisWorkbook.Sheets("Search Export")

    Set OutApp = CreateObject("Outlook.Application")
    
    Dim BodyText As String
    BodyText = ws.Range("G3") & "<BR>" & "<BR>" & _
         ws.Range("G5") & "<BR>" & "<BR>" & _
         ws.Range("G10") & "<BR>" & "<BR>" & _
         ws.Range("G14") & "<BR>" & "<BR>" & _
         ws.Range("G17") & "<BR>" & "<BR>" & _
         ws.Range("G20") & "<BR>" & _
         "<b>" ws.Range("G21") & "</b><br>" & "<BR>" & _
         ws.Range("G22") & "<BR>" & "<BR>" & _
         ws.Range("G24") & "<BR>" & "<BR>" & _
         ws.Range("G26") & "<BR>" & "<BR>" & _
         ws.Range("G28") & "<BR>" & "<BR>" & _
         ws.Range("G30") & "<BR>" & "<BR>" & _
         ws.Range("G32") & "<BR>" & "<BR>" & _
         ws.Range("G34") & "<BR>" & "<BR>" & _
         ws.Range("G36") & "<BR>" & "<BR>" & _
         ws.Range("G38")
   Dim AttachmentName As String
   AttachmentName = FilePath & ws.Cells(2, 6).Value2
         
    For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
      '~~> Constructing addresses and subject
      
      ToAddress = ws.Cells(i, 2).Value2
                  
      CCAddress = ws.Cells(i, 5).Value2
                   
      EmailSubject = ws.Cells(i, 1).Value2
      '~~> This creates a new email (so we can send out multiple emails)
      Set OutMail = OutApp.CreateItem(0)

      With OutMail
         .To = ToAddress
         .CC = CCAddress
         .Subject = EmailSubject
         .HTMLBody = BodyText
         .Attachments.Add AttachmentName
         
         .Display
      
      End With
   Next i

End Sub
 
Upvote 0
You have an ampersand in the wrong place:

Rich (BB code): 
"<b>" & ws.Range("G21") & "</b><br>" & "<BR>" & _
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Dynamic Button for Email Macro
Replies
4
Views
216
GarnesGambit
G
Jyggalag
  • Solved
Help me fix my VBA code (attachment error)
Replies
2
Views
397
Jyggalag
Jyggalag
Jyggalag
  • Solved
Merge multiple email cells into one cell?
Replies
4
Views
2K
Jyggalag
Jyggalag
Jyggalag
  • Question
Change my VBA code so it sends pdf file instead of excel file
Replies
7
Views
805
RoryA
RoryA
S
  • Question
Excel VBA to email multiple ranges as a PDF
Replies
0
Views
668
shophoney
S

Forum statistics

Threads
1,214,975
Messages
6,122,538
Members
449,088
Latest member
RandomExceller01

Share this page

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
Back
Top