My email vba macro does not work

Jyggalag

Jyggalag

Active Member
Joined
Mar 8, 2021
Messages
445
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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

B
  • Question Question
font size in outlook email (12 points doesn't work)
2
Replies
13
Views
288
basb87
B
D
  • Solved
VBA to paste table into Outlook
Replies
5
Views
730
Dani_LobP
D
D
  • Question Question
VBA to Create Outlook Email Not Working for All Users
Replies
9
Views
310
Logit
Logit
H
  • Solved
Macro to extract text to Body in Email and Table at end of Body
Replies
1
Views
596
howard
H
G
  • Solved
Dynamic Button for Email Macro
Replies
4
Views
461
GarnesGambit
G

Forum statistics

Threads
1,223,884
Messages
6,175,174
Members
452,615
Latest member
bogeys2birdies

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