Little change to VBA that sends emails from Excel through Outlook.

Loveexcelll

New Member
Joined
Feb 24, 2022
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Hello,

Can you please help with this VBA? what is want to change is the following:

I want to add new emails every time, but the problem is that the code sends or sees the old emails as new. I want to keep the sended emails as status "Send"
en add new emails to send in the future, so can you change the code to stop sending emails when the status is, "send" in colum F?



VBA Code:
Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mails")
Dim i As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))

For i = 2 To last_row
Set msg = OA.createitem(0)
msg.to = sh.Range("A" & i).Value
msg.cc = sh.Range("B" & i).Value
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value

If sh.Range("E" & i).Value <> "" Then
msg.attachments.Add sh.Range("E" & i).Value
End If

msg.send

sh.Range("F" & i).Value = "[COLOR=rgb(226, 80, 65)]Sent[/COLOR]"

Next i

MsgBox "All the mails have been sent successfully"

End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
If there is no attachment data in Ei, are you still sending the email or not? Makes a difference as to how I think it ought to be written.
 
Upvote 0
If there is no attachment data in Ei, are you still sending the email or not? Makes a difference as to how I think it ought to be written.
Hello, yes the code sends the emails with or without inserting a path for the attachement files.
 
Upvote 0
Maybe like this (untested). You might want to comment out the send line if you want to test without sending. Then you could go back and remove "Sent" from the rows. Assumes F contains (or not) "Sent" and E contains (or not) attachment info.
VBA Code:
Sub Send_Mails()
Dim sh As Worksheet
Dim i As Integer, last_row As Integer, intMsg As Integer
Dim msg As Object, OA As Object

Set sh = ThisWorkbook.Sheets("Send_Mails")
Set OA = CreateObject("outlook.application")

last_row = Application.CountA(sh.Range("A:A"))

For i = 2 To last_row
   Set msg = OA.CreateItem(0)
   With msg
      .To = sh.Range("A" & i)
      .CC = sh.Range("B" & i)
      .Subject = sh.Range("C" & i)
      .Body = sh.Range("D" & i)
      If sh.Range("F" & i) <> "Sent"
         If sh.Range("E" & i) <> "" Then .Attachments.Add sh.Range("E" & i)
         .Send
         intMsg = intMsg + 1
         ''next line would put [COLOR=rgb(226, 80, 65)]Sent[/COLOR] in the cell, not color "Sent"
         ''sh.Range("F" & i) = "[COLOR=rgb(226, 80, 65)]Sent[/COLOR]"
         With sh.Range("F" & i)
            .Font.Color = RGB(226, 8#, 65)
            .Value = "Sent"
         End With
      
      End If
   End With
Next i

If intMsg > 0 Then MsgBox intMsg & " emails were sent."

End Sub
Could also change msgbox line to these 3 lines:
If intMsg > 1 Then MsgBox intMsg & " emails were sent."
If intMsg = 1 Then MsgBox "One email was sent."
If intMsg = 0 Then MsgBox "No emails were sent."

Could also use a Select Case block instead off 3 If's but that would be more than 3 lines of code to do the same thing.
 
Last edited:
Upvote 0
Hello Mi
Maybe like this (untested). You might want to comment out the send line if you want to test without sending. Then you could go back and remove "Sent" from the rows. Assumes F contains (or not) "Sent" and E contains (or not) attachment info.
VBA Code:
Sub Send_Mails()
Dim sh As Worksheet
Dim i As Integer, last_row As Integer, intMsg As Integer
Dim msg As Object, OA As Object

Set sh = ThisWorkbook.Sheets("Send_Mails")
Set OA = CreateObject("outlook.application")

last_row = Application.CountA(sh.Range("A:A"))

For i = 2 To last_row
   Set msg = OA.CreateItem(0)
   With msg
      .To = sh.Range("A" & i)
      .CC = sh.Range("B" & i)
      .Subject = sh.Range("C" & i)
      .Body = sh.Range("D" & i)
      If sh.Range("F" & i) <> "Sent"
         If sh.Range("E" & i) <> "" Then .Attachments.Add sh.Range("E" & i)
         .Send
         intMsg = intMsg + 1
         ''next line would put [COLOR=rgb(226, 80, 65)]Sent[/COLOR] in the cell, not color "Sent"
         ''sh.Range("F" & i) = "[COLOR=rgb(226, 80, 65)]Sent[/COLOR]"
         With sh.Range("F" & i)
            .Font.Color = RGB(226, 8#, 65)
            .Value = "Sent"
         End With
     
      End If
   End With
Next i

If intMsg > 0 Then MsgBox intMsg & " emails were sent."

End Sub
Could also change msgbox line to these 3 lines:
If intMsg > 1 Then MsgBox intMsg & " emails were sent."
If intMsg = 1 Then MsgBox "One email was sent."
If intMsg = 0 Then MsgBox "No emails were sent."

Could also use a Select Case block instead off 3 If's but that would be more than 3 lines of code to do the same thing.

Hello Micron,

I just replaced the code as it is to testing it but when i press the send button i get a "compile error syntax error" for the first line of the code, "Sub Send_Mails()" changes to yellow.

Can you please give me a tip to fixing that error?
 
Upvote 0
Usually the offending part is highlighted somewhere (grey/blue) if it stops on the first line. Make sure your sheet name in the 5th line is spelled correctly. This code runs for me when it's in a module, not behind a sheet. It can matter where you put it.
 
Upvote 0
I have checked everything but still getting the error. So, can you please make a simple file with the code on it and attach this here on the forum?

I really appreciate your help!!
 
Upvote 0
I could, but no. IMO, you need to put that effort into it. That way, the sheet names, sheet data, etc. will be what you have and not what I dream up. That will eliminate you having to modify stuff only to find that it won't work afterwards. I can work with things like modifying email addresses and such in order to fully test. However I don't think you can post files here, but you probably could post an actual sheet using the xl2bb feature here, or maybe post a table, but don't post a picture of data.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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