Modify vb script to not email if no data

Guys I have this script that works for me and e-mails a pdf and word document for everyone in a list the problem I have some times the person getting e-mailed has no info to be emailed with and they recieve a blank pdf.

What I would like is if a specific cell ie   Sheet 'Fuel' cell A10 is blank to not send an e-mail for that person and move to the next in the list

ill attach my code its not the prettiest but it works

Thanks

Sub EmailNow()

Dim ol As Object
Dim myItem As Outlook.MailItem
Dim myMsg1 As String
Dim myMsg2 As String
Dim myMsg3 As String
Dim myMsg4 As String
Dim myMsg5 As String
Dim myMsg6 As String
Dim myMsg7 As String
Dim myMsg8 As String
Dim myMsg9 As String
Dim myMsg10 As String
Dim AddCell As Range
Dim myAtts As Outlook.Attachments
Dim tmpFile As String
Dim tmpFile1 As String
Dim eMailWks As Worksheet
Dim fuelWks As Worksheet
Dim r As Range
Dim rng As Range
Dim rPrintArea As Range


    Set fuelWks = ThisWorkbook.Worksheets("Fuel")
    Set eMailWks = ThisWorkbook.Worksheets("E-mail")
    Set ol = CreateObject("outlook.application")
    

    myMsg1 = "<p><font face=""trebuchet ms"">This is your " & Format(Worksheets("Setup").Range("B1").Value, "Mmmm yyyy") & " Fuel Dashboard.</font></p>" & _
             " " & Chr(10) & _
             "<p><font face=""trebuchet ms"">Attached you will find a personalised PDF which visualises all your fuel card transactions " & _
             "for " & Format(Worksheets("Setup").Range("B1").Value, "Mmmm yyyy") & ". You will see that all of your transactions are individually " & _
             "compared to both the National and the Supermarket Average Price Per Litre.</font></p>" & _
             " " & Chr(10)
    myMsg2 = "<p><b><u><font face=""trebuchet ms""><font color = ""RED"">REMINDER</b></u></p></font>" & Chr(10)
    myMsg3 = "<b><u><font face=""trebuchet ms""><font color = ""RED"">It is policy for all card users to state your EXACT mileage at the point of EVERY transaction. Noncompliance will be noted going forward.</b></u></font>" & Chr(10) & _
             " " & Chr(10)
    myMsg4 = "<p><font face=""trebuchet ms"">Your own buying performance is identified on a colour dial. " & _
             "Green being the best, Amber being acceptable and Red being the least favourable.</p></font>" & _
             " " & Chr(10) & _
             "<p><font face=""trebuchet ms"">The best outcome would be to have everyone purchasing their fuel at Supermarket prices as these are by far the cheapest. " & _
             "Each month the PDF will highlight all up-to-date information relating to your fuel usage and will also include the bonus incentives " & _
             "on offer by National Supermarkets which you the user, can personally claim or collect.</p></font> " & _
             " " & Chr(10) & _
             "<p><font face=""trebuchet ms"">The calculated average takes into account both the price per litre and number of litres purchased. So if it is neccessary to put £20 of fuel in on a " & _
             "motorway it wont necessarily skew your average.</p></font>" & _
             "<p><font face=""trebuchet ms"">Included is a link to a price comparison website which quickly allows you to identify the " & _
             "cheapest fuel stations in your area by simply entering your postcode.</p>" & _
             "This project welcomes user feedback and suggestions and has the full support of our Management Team and Directors.</font></p>" & _
             " " & Chr(10) & _
             "<p><font face=""trebuchet ms"">Please can Site Managers ensure that the information within this e-mail is shared with anybody who may have the use of a company fuel card </p></font>" & _
             "" & Chr(10)
    myMsg5 = "<p><font face=""trebuchet ms"">" & _
             ""
    myMsg6 = "<p><b><u>IMPORTANT</b></p></u>" & Chr(10)
    myMsg7 = "Please correspond if you believe the responsibility of this fuel card does not sit with you." & Chr(10) & _
             " <BR>" & Chr(10)
    myMsg8 = "<p></p><BR>" & Chr(10)
    myMsg9 = "<p><font face=""trebuchet ms"">Regards,</p>" & Chr(10) & _
             " " & Chr(10) & _
             "<p>Caroline Briggs</p>" & _
             "Business Improvement</font>"

      
    myMsg1 = myMsg1 & "" & myMsg2 & "" & myMsg3 & "" & myMsg4 & "" & myMsg5 & "" & myMsg6 & "" & myMsg7 & "" & myMsg8 & "" & myMsg9  'ActiveSheet.Range("c6") & Chr(10)
    
    
    With eMailWks
        Set rng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With
    
    'set print area and details on how to print - 1 page wide and as many long as needed
    With fuelWks
        'Set rPrintArea = .Range("A1", .Range("S" & .Rows.Count).End(xlUp)) 'define print area for PDF output
        Set rPrintArea = .Range("A1", .Range("V35")) 'define print area for PDF output
        With .PageSetup
            .PrintArea = rPrintArea.Address
            .PaperSize = xlPaperA4
            .Orientation = xlLandscape
            .FirstPageNumber = xlAutomatic
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 99
        End With
    End With

    For Each r In rng
        'fuelWks.Range("C6").Value = r.Offset(, 1).Value
        fuelWks.Range("F6").Value = r.Value
        
        
        tmpFile = "c:\temp\" & ActiveSheet.Range("D6") & " " & ActiveSheet.Range("F6") & " fuel costs.pdf" '<-changed to PDF
        'tmpFile1 = "S:\Projects_ Operations\2010\UK 2010-13 - Reduction of Monthly Fuel Expenditure\Fuel\Awareness Letter.docx"
        tmpFile1 = "C:\Users\Darren Jackson.CENTRAL\Documents\caroline\Awareness Letter.docx"
        
        'ThisWorkbook.SaveCopyAs tmpFile
        fuelWks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                tmpFile, Quality:= _
                xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
        
'r.Offset(, 1).Value
        myMsg10 = "<font face=""trebuchet ms"">Hello " & fuelWks.Range("D6") & _
                 ",</font>" & Chr(10) & Chr(10) & myMsg1
        'MsgBox "This is your msg: " & Chr(10) & Chr(10) & myMsg2
        
        Set myItem = ol.CreateItem(olMailItem)
        'set to field
        
        myItem.To = r.Offset(, 1).Value & "<" & r.Offset(, 3).Value & ">"
        myItem.Subject = fuelWks.Range("D6") & " fuel costs"
        myItem.HTMLBody = myMsg10
        Set myAtts = myItem.Attachments
        myAtts.Add tmpFile
        myAtts.Add tmpFile1
        myItem.Display
        'uncomment for direct sending
        'myItem.Send
    Next r

    Set ol = Nothing

End Sub

Open in new window

DarrenJacksonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven CarnahanNetwork ManagerCommented:
untested but try something like this:

Sub EmailNow()

if range("A10") = ""

Dim ol As Object
Dim myItem As Outlook.MailItem
Dim myMsg1 As String
Dim myMsg2 As String
Dim myMsg3 As String
Dim myMsg4 As String
Dim myMsg5 As String
Dim myMsg6 As String
Dim myMsg7 As String
Dim myMsg8 As String
Dim myMsg9 As String
Dim myMsg10 As String
Dim AddCell As Range
Dim myAtts As Outlook.Attachments
Dim tmpFile As String
Dim tmpFile1 As String
Dim eMailWks As Worksheet
Dim fuelWks As Worksheet
Dim r As Range
Dim rng As Range
Dim rPrintArea As Range


    Set fuelWks = ThisWorkbook.Worksheets("Fuel")
    Set eMailWks = ThisWorkbook.Worksheets("E-mail")
    Set ol = CreateObject("outlook.application")
    

    myMsg1 = "<p><font face=""trebuchet ms"">This is your " & Format(Worksheets("Setup").Range("B1").Value, "Mmmm yyyy") & " Fuel Dashboard.</font></p>" & _
             " " & Chr(10) & _
             "<p><font face=""trebuchet ms"">Attached you will find a personalised PDF which visualises all your fuel card transactions " & _
             "for " & Format(Worksheets("Setup").Range("B1").Value, "Mmmm yyyy") & ". You will see that all of your transactions are individually " & _
             "compared to both the National and the Supermarket Average Price Per Litre.</font></p>" & _
             " " & Chr(10)
    myMsg2 = "<p><b><u><font face=""trebuchet ms""><font color = ""RED"">REMINDER</b></u></p></font>" & Chr(10)
    myMsg3 = "<b><u><font face=""trebuchet ms""><font color = ""RED"">It is policy for all card users to state your EXACT mileage at the point of EVERY transaction. Noncompliance will be noted going forward.</b></u></font>" & Chr(10) & _
             " " & Chr(10)
    myMsg4 = "<p><font face=""trebuchet ms"">Your own buying performance is identified on a colour dial. " & _
             "Green being the best, Amber being acceptable and Red being the least favourable.</p></font>" & _
             " " & Chr(10) & _
             "<p><font face=""trebuchet ms"">The best outcome would be to have everyone purchasing their fuel at Supermarket prices as these are by far the cheapest. " & _
             "Each month the PDF will highlight all up-to-date information relating to your fuel usage and will also include the bonus incentives " & _
             "on offer by National Supermarkets which you the user, can personally claim or collect.</p></font> " & _
             " " & Chr(10) & _
             "<p><font face=""trebuchet ms"">The calculated average takes into account both the price per litre and number of litres purchased. So if it is neccessary to put £20 of fuel in on a " & _
             "motorway it wont necessarily skew your average.</p></font>" & _
             "<p><font face=""trebuchet ms"">Included is a link to a price comparison website which quickly allows you to identify the " & _
             "cheapest fuel stations in your area by simply entering your postcode.</p>" & _
             "This project welcomes user feedback and suggestions and has the full support of our Management Team and Directors.</font></p>" & _
             " " & Chr(10) & _
             "<p><font face=""trebuchet ms"">Please can Site Managers ensure that the information within this e-mail is shared with anybody who may have the use of a company fuel card </p></font>" & _
             "" & Chr(10)
    myMsg5 = "<p><font face=""trebuchet ms"">" & _
             ""
    myMsg6 = "<p><b><u>IMPORTANT</b></p></u>" & Chr(10)
    myMsg7 = "Please correspond if you believe the responsibility of this fuel card does not sit with you." & Chr(10) & _
             " <BR>" & Chr(10)
    myMsg8 = "<p></p><BR>" & Chr(10)
    myMsg9 = "<p><font face=""trebuchet ms"">Regards,</p>" & Chr(10) & _
             " " & Chr(10) & _
             "<p>Caroline Briggs</p>" & _
             "Business Improvement</font>"

      
    myMsg1 = myMsg1 & "" & myMsg2 & "" & myMsg3 & "" & myMsg4 & "" & myMsg5 & "" & myMsg6 & "" & myMsg7 & "" & myMsg8 & "" & myMsg9  'ActiveSheet.Range("c6") & Chr(10)
    
    
    With eMailWks
        Set rng = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With
    
    'set print area and details on how to print - 1 page wide and as many long as needed
    With fuelWks
        'Set rPrintArea = .Range("A1", .Range("S" & .Rows.Count).End(xlUp)) 'define print area for PDF output
        Set rPrintArea = .Range("A1", .Range("V35")) 'define print area for PDF output
        With .PageSetup
            .PrintArea = rPrintArea.Address
            .PaperSize = xlPaperA4
            .Orientation = xlLandscape
            .FirstPageNumber = xlAutomatic
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = 99
        End With
    End With

    For Each r In rng
        'fuelWks.Range("C6").Value = r.Offset(, 1).Value
        fuelWks.Range("F6").Value = r.Value
        
        
        tmpFile = "c:\temp\" & ActiveSheet.Range("D6") & " " & ActiveSheet.Range("F6") & " fuel costs.pdf" '<-changed to PDF
        'tmpFile1 = "S:\Projects_ Operations\2010\UK 2010-13 - Reduction of Monthly Fuel Expenditure\Fuel\Awareness Letter.docx"
        tmpFile1 = "C:\Users\Darren Jackson.CENTRAL\Documents\caroline\Awareness Letter.docx"
        
        'ThisWorkbook.SaveCopyAs tmpFile
        fuelWks.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                tmpFile, Quality:= _
                xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
        
'r.Offset(, 1).Value
        myMsg10 = "<font face=""trebuchet ms"">Hello " & fuelWks.Range("D6") & _
                 ",</font>" & Chr(10) & Chr(10) & myMsg1
        'MsgBox "This is your msg: " & Chr(10) & Chr(10) & myMsg2
        
        Set myItem = ol.CreateItem(olMailItem)
        'set to field
        
        myItem.To = r.Offset(, 1).Value & "<" & r.Offset(, 3).Value & ">"
        myItem.Subject = fuelWks.Range("D6") & " fuel costs"
        myItem.HTMLBody = myMsg10
        Set myAtts = myItem.Attachments
        myAtts.Add tmpFile
        myAtts.Add tmpFile1
        myItem.Display
        'uncomment for direct sending
        'myItem.Send
    Next r

    Set ol = Nothing

endif

End Sub

Open in new window

0
DarrenJacksonAuthor Commented:
Hi wont this just stop running if the code detects that A10 is blank i need it to keep cycling through the list until it gets to the end

Thanks for helping
0
Steven CarnahanNetwork ManagerCommented:
Yes - I didn't read through your code that much.  I just thought that you didn't want it to execute at all.  

Try it this way:

if range("A10") = ""

      'uncomment for direct sending
        'myItem.Send
endif
    Next r

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DarrenJacksonAuthor Commented:
That's great thanks it works how I need it to
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.