Problems using Outlook from Excel via macro

Y

Yamezz

Active Member
Joined
Nov 22, 2006
Messages
367
Office Version
  1. 2019
I have two macros, that are very similar, using VBA to create an invoice and attach it to an email. One works, one does not and I cannot see what I've done wrong.
The macro that works steps through a table of customer data, writing and invoice, creating a pdf and emailing it one by one until it runs out of customers in the table. The one that doesn't work is a macro that creates a single invoice from user-inputted details and is supposed to attach that pdf and email it. It does the invoice, saves a pdf, but won't do any of the Outlook bit.

For anyone willing to help me, what do you need? - I can post both lots of code, but they're a couple of pages long each.
 

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
.
Might be better to post your workbooks for download to DROPBOX.com or similar.
 
Upvote 0
Hmm, it's a big file, with years of financial data. I might have a go at creating a stripped-down version to post.
 
Upvote 0
.
Yes ... just enough sample information to present the overall concept of your project.

Leave out any confidential information ... just provide psuedo data.
 
Upvote 0
OK, my attempt to strip the file down is perhaps making the issue more complicated. I'll post both macros and hopefully someone can see why one is working and on isn't.
RecordSale is giving a 1004 error, but RecordSale2 is not.
I've been through the comparison line by line, but I'm not smart enough to see what difference is causing my problem.
Code: 
Sub RecordSale()

On Error GoTo ErrorHandler

Application.ScreenUpdating = False
Sheets("Sale").Unprotect
Sheets("Sale Records").Unprotect
Sheets("AR").Unprotect
Sheets("HG Invoice").Unprotect


Dim LastARRow As Long, LastSaleRecordsRow As Long, NewLastSalesRecordsRow As Long
Dim Customer As String, CustEmail As String, ClientCode As String
Dim InvDate As Date

Customer = Sheets("Sale").Range("Customer").Value
CustEmail = Application.WorksheetFunction.VLookup(Customer, Sheets("Customers").Range("A2:I50"), 9, False)
ClientCode = Application.WorksheetFunction.VLookup(Customer, Sheets("Customers").Range("A2:I50"), 2, False)

' DETERMINE INVOICE NUMBER AND PASTE
Dim Inv As Long, NewInv As Long
LastSaleRecordsRow = Worksheets("Sale Records").UsedRange.Rows.Count 'determines the # of rows used
Inv = Worksheets("Sale Records").Cells(LastSaleRecordsRow, 1) 'determines the last used Inv #
NewInv = Inv + 1 'increase Inv # by one
Range("SaleInvNo") = NewInv 'Copies new Inv # to the Sale sheet
Sheets("Sale Records").Cells(LastSaleRecordsRow + 1, 1) = NewInv 'Copies new INV # to the Sale Records sheet
LastARRow = Worksheets("AR").UsedRange.Rows.Count 'determines the # of rows used
Sheets("AR").Cells(LastARRow + 1, 1) = NewInv 'Copies new INV # to the AR sheet

'COPY DETAILS FROM SALE SHEET TO AR AND SALE RECORDS
Sheets("AR").Cells(LastARRow + 1, 2) = Sheets("Sale").Range("SaleDate") 'copies Date
Sheets("Sale Records").Cells(LastSaleRecordsRow + 1, 2) = Sheets("Sale").Range("SaleDate") 'copies Date
Sheets("AR").Cells(LastARRow + 1, 3) = Sheets("Sale").Range("Customer") 'copies Customer
Sheets("Sale Records").Cells(LastSaleRecordsRow + 1, 3) = Sheets("Sale").Range("Customer") 'copies Customer
Sheets("AR").Cells(LastARRow + 1, 4) = Sheets("Sale").Range("SaleTotal") 'copies GST inc Value
Sheets("AR").Cells(LastARRow + 1, 6) = Sheets("Sale").Range("SaleGST") 'copies GST
Sheets("AR").Cells(LastARRow + 1, 7) = Sheets("Sale").Range("SaleDueDate") 'copies Due Date
Sheets("AR").Cells(LastARRow + 1, 5).FormulaR1C1 = "=RC[-1]-RC[+5]-RC[+10]-RC[+15]" 'inputs formula to calculate amount owing
Sheets("AR").Cells(LastARRow + 1, 8).FormulaR1C1 = "=IF(RC[-3]>0,TODAY()-RC[-1],"""")" 'inputs formula to calculate overdue
Sheets("AR").Cells(LastARRow + 1, 12).FormulaR1C1 = "=IF(RC[-2]<>0,RC[-2]/RC[-8]*RC[-6],"""")" 'inputs formula to calculate GST portion of payment
Sheets("AR").Cells(LastARRow + 1, 17) = Sheets("AR").Cells(LastARRow + 1, 12) 'copies GST formula to 2nd payment
Sheets("AR").Cells(LastARRow + 1, 22) = Sheets("AR").Cells(LastARRow + 1, 12) 'copies GST formula to 3rd payment
If Range("SalePaidToday") <> 0 Then
    Sheets("AR").Cells(LastARRow + 1, 9) = Sheets("Sale").Range("SaleDate")
    Sheets("AR").Cells(LastARRow + 1, 10) = Sheets("Sale").Range("SalePaidToday")
    Sheets("AR").Cells(LastARRow + 1, 11) = Sheets("Sale").Range("SalePaymentMethod")
    Sheets("AR").Cells(LastARRow + 1, 13) = Sheets("Sale").Range("SalePaymentAccount")
End If

'COPY SaleDetails TO SALE RECORDS
With Range("SaleDetails")
    If Not IsEmpty(.Cells(2, 1)) Then
    Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Resize(, 5).Copy
    Else: .Cells(1, 1).Resize(, 5).Copy
    End If
End With
Sheets("Sale Records").Cells(LastSaleRecordsRow + 1, 4).PasteSpecial Paste:=xlPasteAllExceptBorders

'DELETE DATA VALIDATION
NewLastSalesRecordsRow = Worksheets("Sale Records").UsedRange.Rows.Count ' determine NewLastSaleRecordsRow
Range(Cells(LastSaleRecordsRow + 1, 5), Cells(NewLastSalesRecordsRow, 6)).Validation.Delete

'FILL INV#, DATE AND CUSTOMER TO MATCH SALE DESCRIPTION ENTRIES
Sheets("Sale Records").Select
Dim RowA As Long, RowD As Long
RowA = Range("A" & Rows.Count).End(xlUp).Row
RowD = Range("D" & Rows.Count).End(xlUp).Row
If RowD > RowA Then
    Range("A" & RowA, "C" & RowD).FillDown
End If

'CLEAR DETAILS FROM PREVIOUS INVOICE
Sheets("HG Invoice").Range("TaxInvDescription2").ClearContents
Sheets("HG Invoice").Range("TaxInvAmount2").ClearContents
Sheets("HG Invoice").Range("TaxInvTax2").ClearContents
Sheets("HG Invoice").Range("TaxInvPaid").ClearContents
Sheets("HG Invoice").Range("TaxInvCustABN").ClearContents
Sheets("HG Invoice").Range("TaxInvPostCode").ClearContents
Sheets("HG Invoice").Range("TaxInvState").ClearContents
Sheets("HG Invoice").Range("TaxInvSuburb").ClearContents
Sheets("HG Invoice").Range("TaxInvAddress").ClearContents

'   CREATE INVOICE FROM SALE DATA
Sheets("HG Invoice").Range("TaxInvDate") = Sheets("Sale").Range("SaleDate")
Sheets("HG Invoice").Range("TaxInvNo") = Sheets("Sale").Range("SaleInvNo")
Sheets("HG Invoice").Range("TaxInvTerms") = Sheets("Sale").Range("SaleTerms")
Sheets("HG Invoice").Range("TaxInvDueDate") = Sheets("Sale").Range("SaleDueDate")
Sheets("HG Invoice").Range("TaxInvSubtotal") = Sheets("Sale").Range("SaleSubtotal")
Sheets("HG Invoice").Range("TaxInvGST") = Sheets("Sale").Range("SaleGST")
Sheets("HG Invoice").Range("TaxInvTotal") = Sheets("Sale").Range("SaleTotal")
Sheets("HG Invoice").Range("TaxInvPaid") = Sheets("Sale").Range("SalePaidToday")
Sheets("HG Invoice").Range("TaxInvCustomer") = Sheets("Sale").Range("Customer")

' COPY CUSTOMER DATA TO INVOICE
Dim CustRow As Long
Dim c As Range

With Range("CustomerName")
    Set c = .Find(What:=Customer, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    CustRow = c.Row 'if code errors out here, customer name in Cellar is not EXACTLY the same as Customer range in Accounting

End With

Sheets("HG Invoice").Range("TaxInvAddress") = Sheets("Customers").Cells(CustRow, 3)
Sheets("HG Invoice").Range("TaxInvSuburb") = Sheets("Customers").Cells(CustRow, 4)
Sheets("HG Invoice").Range("TaxInvState") = Sheets("Customers").Cells(CustRow, 5)
Sheets("HG Invoice").Range("TaxInvPostCode") = Sheets("Customers").Cells(CustRow, 6)
Sheets("HG Invoice").Range("TaxInvCustABN") = Sheets("Customers").Cells(CustRow, 7)

'COPY SaleDetails TO INVOICE
With Range("SaleDetails")
        If Not IsEmpty(.Cells(2, 1)) Then 'if more than one item in SaleDetails
            Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Resize(, 1).Copy 'copy Description
            Sheets("HG Invoice").Range("TaxInvDescription").PasteSpecial Paste:=xlPasteAllExceptBorders
            Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Offset(, 3).Copy 'copy Amounts
            Sheets("HG Invoice").Range("TaxInvAmount").PasteSpecial Paste:=xlPasteAllExceptBorders
            Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Offset(, 4).Copy 'copy Tax values
            Sheets("HG Invoice").Range("TaxInvTax").PasteSpecial Paste:=xlPasteAllExceptBorders
            'Else, if only one item in SaleDetails
            Else: .Cells(1, 1).Copy 'copy Description
            Sheets("HG Invoice").Range("TaxInvDescription").PasteSpecial Paste:=xlPasteAllExceptBorders
            .Cells(1, 1).Offset(, 3).Copy 'copy Amount
            Sheets("HG Invoice").Range("TaxInvAmount").PasteSpecial Paste:=xlPasteAllExceptBorders
            .Cells(1, 1).Offset(, 4).Copy 'copy Tax value
            Sheets("HG Invoice").Range("TaxInvTax").PasteSpecial Paste:=xlPasteAllExceptBorders
        End If
              
End With

' EXPORT INVOICE AS PDF
Sheets("HG Invoice").Select
    PDFFile = "C:\Users\HawkersGate Win7\Documents\" & "HGInv" & NewInv & " " & Customer & ".pdf"
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With

' RESET part of Sale sheet
Sheets("Sale").Range(SalePaidToday).Clear contents
Sheets("Sale").Range(SalePaymentMethod).Clear contents
Sheets("Sale").Range(SalePaymentAccount).Clear contents

' REPROTECT Sheets
Sheets("Sale").Select
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells

Sheets("Sale Records").Select
ActiveSheet.Protect , AllowFiltering:=True

Sheets("HG Invoice").Select
ActiveSheet.Protect

Sheets("AR").Select
ActiveSheet.Protect
ActiveSheet.Protect , AllowFiltering:=True

  
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = "Invoice " & NewInv & " from Hawkers Gate"
    .To = CustEmail ' <-- Put email of the recipient here
    .Body = "Latest invoice attached." & vbLf & vbLf _
          & "Cheers," & vbLf _
          & "James."
    .attachments.Add PDFFile
       
    ' Try to send
    On Error Resume Next
    '.Send    THIS WILL SEND IMMEDIATELY
    Application.Visible = True
    .Display 'THIS WILL SEND LATER
    

   
  End With
 
  Set OutlApp = Nothing ' Release the memory of object variable

ErrorHandler:
If Err.Number = 1004 Then
    MsgBox "Customer not found.  Please set up new customer"
End If

Application.ScreenUpdating = True

End Sub

Code: 
Sub RecordSale2()

' SECOND VERSION OF RECORD SALE WITH OPTION TO ATTACH MONTHLY SUMMARY FILE

On Error GoTo ErrorHandler

Application.ScreenUpdating = False
Sheets("Sale").Unprotect
Sheets("Sale Records").Unprotect
Sheets("AR").Unprotect
Sheets("HG Invoice").Unprotect


Dim LastARRow As Long, LastSaleRecordsRow As Long, NewLastSalesRecordsRow As Long
Dim Customer, CustEmail, ClientCode, InvMo, InvYear As String
Dim InvDate As Date

Customer = Sheets("Sale").Range("Customer").Value
CustEmail = Application.WorksheetFunction.VLookup(Customer, Sheets("Customers").Range("A2:I50"), 9, False)
ClientCode = Application.WorksheetFunction.VLookup(Customer, Sheets("Customers").Range("A2:I50"), 2, False)

' DETERMINE INVOICE NUMBER AND PASTE
Dim Inv As Long, NewInv As Long
LastSaleRecordsRow = Worksheets("Sale Records").UsedRange.Rows.Count 'determines the # of rows used
Inv = Worksheets("Sale Records").Cells(LastSaleRecordsRow, 1) 'determines the last used Inv #
NewInv = Inv + 1 'increase Inv # by one
Range("SaleInvNo") = NewInv 'Copies new Inv # to the Sale sheet
Sheets("Sale Records").Cells(LastSaleRecordsRow + 1, 1) = NewInv 'Copies new INV # to the Sale Records sheet
LastARRow = Worksheets("AR").UsedRange.Rows.Count 'determines the # of rows used
Sheets("AR").Cells(LastARRow + 1, 1) = NewInv 'Copies new INV # to the AR sheet

'COPY DETAILS FROM SALE SHEET TO AR AND SALE RECORDS
Sheets("AR").Cells(LastARRow + 1, 2) = Sheets("Sale").Range("SaleDate") 'copies Date
Sheets("Sale Records").Cells(LastSaleRecordsRow + 1, 2) = Sheets("Sale").Range("SaleDate") 'copies Date
Sheets("AR").Cells(LastARRow + 1, 3) = Sheets("Sale").Range("Customer") 'copies Customer
Sheets("Sale Records").Cells(LastSaleRecordsRow + 1, 3) = Sheets("Sale").Range("Customer") 'copies Customer
Sheets("AR").Cells(LastARRow + 1, 4) = Sheets("Sale").Range("SaleTotal") 'copies GST inc Value
Sheets("AR").Cells(LastARRow + 1, 6) = Sheets("Sale").Range("SaleGST") 'copies GST
Sheets("AR").Cells(LastARRow + 1, 7) = Sheets("Sale").Range("SaleDueDate") 'copies Due Date
Sheets("AR").Cells(LastARRow + 1, 5).FormulaR1C1 = "=RC[-1]-RC[+5]-RC[+10]-RC[+15]" 'inputs formula to calculate amount owing
Sheets("AR").Cells(LastARRow + 1, 8).FormulaR1C1 = "=IF(RC[-3]>0,TODAY()-RC[-1],"""")" 'inputs formula to calculate overdue
Sheets("AR").Cells(LastARRow + 1, 12).FormulaR1C1 = "=IF(RC[-2]<>0,RC[-2]/RC[-8]*RC[-6],"""")" 'inputs formula to calculate GST portion of payment
Sheets("AR").Cells(LastARRow + 1, 17) = Sheets("AR").Cells(LastARRow + 1, 12) 'copies GST formula to 2nd payment
Sheets("AR").Cells(LastARRow + 1, 22) = Sheets("AR").Cells(LastARRow + 1, 12) 'copies GST formula to 3rd payment

'COPY SaleDetails TO SALE RECORDS
With Range("SaleDetails")
    If Not IsEmpty(.Cells(2, 1)) Then
    Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Resize(, 5).Copy
    Else: .Cells(1, 1).Resize(, 5).Copy
    End If
End With
Sheets("Sale Records").Cells(LastSaleRecordsRow + 1, 4).PasteSpecial Paste:=xlPasteAllExceptBorders

'DELETE DATA VALIDATION
NewLastSalesRecordsRow = Worksheets("Sale Records").UsedRange.Rows.Count ' determine NewLastSaleRecordsRow
Range(Cells(LastSaleRecordsRow + 1, 5), Cells(NewLastSalesRecordsRow, 6)).Validation.Delete

'FILL INV#, DATE AND CUSTOMER TO MATCH SALE DESCRIPTION ENTRIES
Sheets("Sale Records").Select
Dim RowA As Long, RowD As Long
RowA = Range("A" & Rows.Count).End(xlUp).Row
RowD = Range("D" & Rows.Count).End(xlUp).Row
If RowD > RowA Then
    Range("A" & RowA, "C" & RowD).FillDown
End If

'CLEAR DETAILS FROM PREVIOUS INVOICE
Sheets("HG Invoice").Range("TaxInvDescription2").ClearContents
Sheets("HG Invoice").Range("TaxInvAmount2").ClearContents
Sheets("HG Invoice").Range("TaxInvTax2").ClearContents
Sheets("HG Invoice").Range("TaxInvPaid").ClearContents

'   CREATE INVOICE FROM SALE DATA
Sheets("HG Invoice").Range("TaxInvDate") = Sheets("Sale").Range("SaleDate")
InvDate = Sheets("Sale").Range("SaleDate").Value
Sheets("HG Invoice").Range("TaxInvNo") = Sheets("Sale").Range("SaleInvNo")
Sheets("HG Invoice").Range("TaxInvTerms") = Sheets("Sale").Range("SaleTerms")
Sheets("HG Invoice").Range("TaxInvDueDate") = Sheets("Sale").Range("SaleDueDate")
Sheets("HG Invoice").Range("TaxInvSubtotal") = Sheets("Sale").Range("SaleSubtotal")
Sheets("HG Invoice").Range("TaxInvGST") = Sheets("Sale").Range("SaleGST")
Sheets("HG Invoice").Range("TaxInvTotal") = Sheets("Sale").Range("SaleTotal")
Sheets("HG Invoice").Range("TaxInvPaid") = Sheets("Sale").Range("SalePaidToday")
Sheets("HG Invoice").Range("TaxInvCustomer") = Sheets("Sale").Range("Customer")

' COPY CUSTOMER DATA TO INVOICE
Dim CustRow As Long
Dim c As Range

With Range("CustomerName")
    Set c = .Find(What:=Customer, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    CustRow = c.Row 'if code errors out here, customer name in Cellar is not EXACTLY the same as Customer range in Accounting

End With

Sheets("HG Invoice").Range("TaxInvAddress") = Sheets("Customers").Cells(CustRow, 3)
Sheets("HG Invoice").Range("TaxInvSuburb") = Sheets("Customers").Cells(CustRow, 4)
Sheets("HG Invoice").Range("TaxInvState") = Sheets("Customers").Cells(CustRow, 5)
Sheets("HG Invoice").Range("TaxInvPostCode") = Sheets("Customers").Cells(CustRow, 6)
Sheets("HG Invoice").Range("TaxInvCustABN") = Sheets("Customers").Cells(CustRow, 7)

'COPY SaleDetails TO INVOICE
With Range("SaleDetails")
        If Not IsEmpty(.Cells(2, 1)) Then 'if more than one item in SaleDetails
            Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Resize(, 1).Copy 'copy Description
            Sheets("HG Invoice").Range("TaxInvDescription").PasteSpecial Paste:=xlPasteAllExceptBorders
            Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Offset(, 3).Copy 'copy Amounts
            Sheets("HG Invoice").Range("TaxInvAmount").PasteSpecial Paste:=xlPasteAllExceptBorders
            Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).Offset(, 4).Copy 'copy Tax values
            Sheets("HG Invoice").Range("TaxInvTax").PasteSpecial Paste:=xlPasteAllExceptBorders
            'Else, if only one item in SaleDetails
            Else: .Cells(1, 1).Copy 'copy Description
            Sheets("HG Invoice").Range("TaxInvDescription").PasteSpecial Paste:=xlPasteAllExceptBorders
            .Cells(1, 1).Offset(, 3).Copy 'copy Amount
            Sheets("HG Invoice").Range("TaxInvAmount").PasteSpecial Paste:=xlPasteAllExceptBorders
            .Cells(1, 1).Offset(, 4).Copy 'copy Tax value
            Sheets("HG Invoice").Range("TaxInvTax").PasteSpecial Paste:=xlPasteAllExceptBorders
        End If
        
End With

' EXPORT INVOICE AS PDF
Sheets("HG Invoice").Select
    PDFFile = "C:\Users\HawkersGate Win7\Documents\" & "HGInv" & NewInv & " " & Customer & ".pdf"
        With ActiveSheet
            .ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With

' RESET SALE SHEET FOR NEW SALE
'Range("SaleDetails").ClearContents
'Sheets("Sale").Range("SaleTax").FormulaR1C1 = "=IF(RC[-1]<>0,ROUND(RC[-1]*0.1,2),"""")"

' REPROTECT Sheets
Sheets("Sale").Select
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells

Sheets("Sale Records").Select
ActiveSheet.Protect , AllowFiltering:=True

Sheets("HG Invoice").Select
ActiveSheet.Protect

Sheets("AR").Select
ActiveSheet.Protect
ActiveSheet.Protect , AllowFiltering:=True

'DETERMINE CELLAR INVOICE MONTH AND YEAR FROM INVOICE DATE & ASSUME IT WAS FROM LAST MONTH
InvMo = Format(DateAdd("m", -1, Sheets("Sale").Range("SaleDate").Value), "mmm")
InvYear = Format(DateAdd("m", -1, Sheets("Sale").Range("SaleDate").Value), "yy")

  
  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

 
  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)
   
    ' Prepare e-mail
    .Subject = "Invoice " & NewInv & " from Hawkers Gate"
    .To = CustEmail ' <-- Put email of the recipient here
    .Body = "Latest invoice attached." & vbLf & vbLf _
          & "Cheers," & vbLf _
          & "James."
    .attachments.Add PDFFile
    If Dir("C:\Users\HawkersGate Win7\Documents\" & ClientCode & " " & InvMo & InvYear & ".xlsx") <> "" Then
        .attachments.Add ("C:\Users\HawkersGate Win7\Documents\" & ClientCode & " " & InvMo & InvYear & ".xlsx")
    End If
   
    ' Try to send
    On Error Resume Next
    '.Send    'THIS WILL SEND IMMEDIATELY
    Application.Visible = True
    .Display 'THIS WILL SEND LATER
    

   
  End With
 
  Set OutlApp = Nothing ' Release the memory of object variable

ErrorHandler:
If Err.Number = 1004 Then
    MsgBox "Customer not found.  Please set up new customer"
End If

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Where in the code do you get the error?
 
Upvote 0
My ErrorHandler message comes up "Customer not found", but that's it. The code just finishes after clicking OK - Outlook is not called up. Whereas, the second one switches to Outlook and brings up the email addressed and attached as proper.
 
Upvote 0
Either remove the error handler or comment it out.

Then you should find the problem line of code and hopefully that will shed some light on the root of the problem.
 
Upvote 0
Thanks Norie. The line
Code: 
Sheets("Sale").Range(SalePaidToday).ClearContents
is highlighted with a 1004 error.

A simple lack of inverted commas seems to have been my problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Copy emails from specific folder in Outlook to a shared local folder
Replies
1
Views
227
feni1388
F
A
  • Question Question
Outlook File Attachment using VBA
2 3
Replies
22
Views
3K
RoryA
RoryA
J
  • Solved
Save outlook attachment without file extensions
Replies
3
Views
843
johnnyL
J
R
  • Question Question
Mail merge to insert the picture in the mail body and schedule the email
Replies
0
Views
1K
rafeek4u
R
B
  • Question Question
Automating Outlook Using Custom Tags in Email Templates
Replies
0
Views
586
brncao
B

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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