email macro warning box removal

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
515
Hi guys

I use the following macro to automatically save and send an active workbook via email

Sub eMailActiveWorkbook()
Dim NewName As String
NewName = Sheets("DECS").Range("C4") & " - " & Sheets("DECS").Range("J4")

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\neilp\Desktop\" & NewName & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
Application.DisplayAlerts = True
Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
Wb.Save
With EmailItem
.Subject = "Trolley sheet"
.Body = "" & vbCrLf & _
"" & vbCrLf & _
""
.To = "user@email.co.uk"
.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Wb.FullName
.Send
End With

Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing

End Sub

It works great, but whilst sending, it flashes up a dialogue box saying that "excel is trying to send email, do you want to continue" Clicking yes allows the programme to continue sending. I would prefer the code to run without it asking for this confirmation. Is that at all possible?


Thanks


Neilp
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
515
thanks Masim

That clicks the yes button but doesnt actually get rid of the warning

Thanks anyway its a start


n
 

Alantar

New Member
Joined
Feb 22, 2005
Messages
8
Hi.

Did you find any solution to your problem?

I am emailing tasks with Excel and outlook 2003 moans that an application is trying to email. A dialog box then asks them to allow access for 1 minute.

I need to get rid of this warning.

Any ideas?

Alantar
 

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
515
Sorry Alantar

The above "solution" is the best I've come across yet. I think its more of an outlook problem than an excel problem. I think in older versions of outlook you could disable the warning box though thanks to 2003 this now facility now appears to have been removed :cry:


n
 

Watch MrExcel Video

Forum statistics

Threads
1,127,357
Messages
5,624,225
Members
416,017
Latest member
moritz210

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
Top