Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

Help with VBA macro multiple emails with attachments

0

Hi All

I am trying to create a VBA Macro to send individual emails to many customers from one workbook.

Their sales information is stored on separate worksheets as you can see in the attached file.

I am trying to send an HTML email with information if they have sales for a certain day 

for example is Valda makes sales in one day then she will receive the worksheet 002-Valda - if no sales are made then no email will be sent.

I hope this is clear and please ask if I am missing information.


Thanks in advance for your help from sunny england

Mark

Answer
Discuss

Discussion

Mark. 

Cant see the file- please edit your question to attach a representative Excel file (using the Add Files... button). I

t might  be sunny but it's a bit parky in the Midlands today! 
John_Ru (rep: 2867) Jan 12, '22 at 11:23 am
Hi John
It is really quite cold here too :) 
Hopefully that is added now.

Thank you 
Mark Battistini (rep: 2) Jan 12, '22 at 11:54 am
Add to Discussion

Answers

0
Selected Answer

Mark

My suggestion is that you loop through worksheets using the code below (added as Module 6 in the revised file attached) which can be run from the button "Advise Sellers" on the Daily... sheet.

It runs though all sheets (ignoring the 2 mentioned) and, if there's a sale (indicated by something in cell A8) adds that to a string (and prints it to the Immediate window). Once all sheets have been examined, it gives a message (but you can inspect/check the results in the Immediate window):

Sub OnlySales()

Dim ws As Worksheet, Sellers As String

'Loop though sheets
For Each ws In ThisWorkbook.Sheets

    If ws.Name = Sheet1.Name Or ws.Name = Sheet17.Name Then
        '  do nothing for those (Daily Sales... and Trader info...)
        Else
        ' check if first sale is on the sheet
        If ws.Range("A8").Value <> "" Then
            'add the string
            Sellers = Sellers & ws.Name & ", "
            ' print to VB Explorer's Immediate window
            Debug.Print ws.Name
        End If
    End If

Next ws
' confirm list
MsgBox "Sent emails to " & Sellers

End Sub
You could replace the lines in bold with ones to create an individual email. I'd suggest you put the seller's email somewhere on their individual sheet e.g. in row 1 (which seems to be hidden in most) so your code within the Else portion can include lines like (from Module 4):
' Build and Send the Email
With emailItem
            .To = ws.Range("A1").Value
Hope this helps.
Discuss

Discussion

Hi John

Thank you so much, I will give this a try and read through to see how much or it I understand. :)

This will be such a great help 
Mark Battistini (rep: 2) Jan 13, '22 at 7:19 am
Good luck Mark and thanks for selecting my Answer. If you get stuck, you can always ask another question.
John_Ru (rep: 2867) Jan 13, '22 at 7:51 am
I will amost certainly get stuck 

Thanks again
Mark Battistini (rep: 2) Jan 13, '22 at 11:29 am
But you get the idea, right? (Looping sheets then when A8 isn't empty, you create an email from that sheet, optionally putting the recipient address in row 1 as I suggested)
John_Ru (rep: 2867) Jan 13, '22 at 11:32 am
Hi again John.  yes I understand it now.  I will work through it tomorrow and hopefully it be ready to use.  That will save hours of work.  thank you again.   
Mark Battistini (rep: 2) Jan 13, '22 at 11:39 pm
Hi John
I am not too sure how to insert the code with my other code on Module 4 of the VBA. 

Also not too sure how to phrase a new question on this matter.     
not sure if you can answer this or need a new question.

Thanks again for your kind help

Mark
Mark Battistini (rep: 2) Jan 15, '22 at 11:56 am
@ Mark  I just noticed that your Module 4 comments show that the code comes from Don's "Excel Email Macro Course" (a paid course) which I haven't done but believe that the code should not be shared on this Forum and that Don sugested questions on that be raised via the Contact page.In the Contact section, Don has set up a category for Premium Courses- suggest you ask him there if the Forum can be used (feel free to refer to my Answer but I was suggesting a different approach before I realised that and I'd rather not confuse learning matters.)

@ Don - please respond to the above (here or to Mark via the Contact page). If you're okay using this vehicle, I can show Mark or integrate my approach into Module 4
John_Ru (rep: 2867) Jan 15, '22 at 1:54 pm
Thank you John
Sorry I did not realise these could not be shared, 

I have purchased the course and the subsequent course too, Although i have not had chance to look at that yet. 

Thank you again 
Mark Battistini (rep: 2) Jan 15, '22 at 2:39 pm
Mark, no problem. I'm not sure but just protecting Don's efforts in creating the Premium courses. I'm sure he expects questions - any worthwhile training always produces them! 
John_Ru (rep: 2867) Jan 15, '22 at 3:43 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login