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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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

i suspect it is the macro security in your outlook.
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?

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

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.
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


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
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?
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.