macro error??

Hi all

Attached is a macro enabled document containing a number of rows containing information. The macro had been designed so that when the "press to send terms to suppliers" button is clicked that particular row of information plus the column headings will be collated together onto a separate spreadsheet and will then be emailed as an attachment to the relevant email address located in column "T". However when I click the button nothing is sending.

Can anyone fix?
Who is Participating?
Martin LissOlder than dirtCommented:
which bit of the code would I have to change to do this?
Change line 38 as shown.
Sub test()

Dim original_wb As Workbook
Dim new_wb As Workbook

Set original_wb = Workbooks.Open("P:\THS Direct Admin\DIRECT supplier Terms\Copy of DIRECT Central Terms encrypted.xlsm") 'adjust file location

row_count = original_wb.Sheets(1).UsedRange.Rows.Count
col_count = original_wb.Sheets(1).UsedRange.Columns.Count

For i = 2 To row_count Step 1
    attachname = "THS_Direct_Trading_Terms_Contact_Details.xlsx"
    emailaddress = original_wb.Sheets(1).Cells(i, 20).Value 'email address from column T
    If emailaddress <> "" Then
        Set new_wb = Workbooks.Add
        original_wb.Sheets(1).Range("A1").EntireRow.Copy Destination:=new_wb.Sheets(1).Range("A1")
        original_wb.Sheets(1).Range("A" & i).EntireRow.Copy Destination:=new_wb.Sheets(1).Range("A2")
        Application.DisplayAlerts = False
        new_wb.SaveAs Environ("temp") & "\" & attachname
        new_wb.Close SaveChanges:=False
        Set new_wb = Nothing
        Application.DisplayAlerts = True
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
                .To = emailaddress
                .CC = ""
                .BCC = ""
                .Subject = "This is the Subject line" 'adjust subjectline!
                .Body = "Dear Supplier," & vbCrLf & vbCrLf & "Attached are the terms/details we hold on file for your current trading terms and contact details with THS direct. Please can you confirm these details are correct by placing a 1 in cell Z3." & vbCrLf & "If there are any differences please overwrite the current terms in red font." & vbCrLf & "Once confirmed please return the complete spreadsheet to" & vbCrLf & vbCrLf & "These terms will be incorporated into our THS Supplier Buying Agreement (SBA) which will subsequently be sent to yourselves to sign and return." & vbCrLf & vbCrLf & "Rob"
                .Attachments.Add (Environ("temp") & "\" & attachname)
                .display '.Send
        End With
        Set OutMail = Nothing
        Set OutApp = Nothing
        Kill Environ("temp") & "\" & attachname
    End If

Open in new window

Martin LissOlder than dirtCommented:
It sends email when I try it. Change .Send to .Display if you want to test it.
can you check the macro security on your Microsoft Outlook?

i suspect it is the macro security in your outlook.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

mikes6058Author Commented:
I have enabled all macros on both excel and outlook trust centres but it still wont send the emails....

when I click the button on the sheet nothing seems to initiate. Before hand it was working fine. I can't think why the emails wont send?

mikes6058Author Commented:
I am looking to adjust the coding so it will do what's outlined in the question in the link below.

Perhaps you may prefer to focus on this question as it should address both issues.
Martin LissOlder than dirtCommented:
Did you try changing .Send to .Display and then run it?
mikes6058Author Commented:
which bit of the code would I have to change to do this?
Martin LissOlder than dirtCommented:
You could also put a breakpoint at the Set new_wb = Workbooks.Add line by clicking in the left-hand margin of that line and when and if the code gets there, press F8 repeatedly to step through the code line by line to see what's happening. You may find my article on debugging useful.
mikes6058Author Commented:
I've changed the line as shown but still get the same result where nothing appears to happen and no emails are sent.
Martin LissOlder than dirtCommented:
What happened when you tried my suggestion in post ID 40721152?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.