Chicken and egg problem - DisplayAlerts

C

ChuckDrago

Active Member
Joined
Sep 7, 2007
Messages
470
Office Version
  1. 2010
Platform
  1. Windows
Once again to the Oracle of Mr.Excel...
I have this Excel form (actually worksheet designed as a form, selectable from our Intranet) which will be filled in by the first user and then approved by two more people downstream. I need to preserve the data throughout, including the approvals, so that the end form reaches the executing operative fully completed (initial data and both approvals).

I elected to Save the form using a SaveAs statement and change filename, so that the initial empty template remains empty for anyone else starting this process. That saving process issues a message about "filename already exists. Do you wish to replace...etc..etc." every time the form is e-mailed.

To eliminate the alerts, I bracketed the SaveAs with disabling and re-enabling the DisplayAlerts. Unfortunately, they resulted in a runtime 400 error. Eliminating the DisplayAlerts lines (bolded below) makes the routine run as intended, but it restores the need of responding to a message every time.

Is there something I could to to achieve no alerts and no runtime error?

The code of the e-mailing routine starts as follows:

Sub EMailer()
Dim OutApp As Object
Dim OutMail As Object
Dim OutMsg As String
Dim Copyto As String
Application.DisplayAlerts = False
ThisWorkbook.SaveAs "My_POReq.xls"
Application.DisplayAlerts = True
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
What's the error message? Because DisplayAlerts in and of itself shouldn't cause any errors.

Is it possible you're running into a problem with Outlook Redemption? (the "An application is trying to send an e-mail...do you want to allow it?" message).

In the future you should also continue in your Original Post. ;)
 
Upvote 0
It is a 400 error.
Quite frankly, I am under the impression that I have made a blunder somewhere and can't see it... Here is the whole routine, Smitty... Please point me in the right direction.

Sub EMailer()

Dim OutApp As Object
Dim OutMail As Object
Dim OutMsg As String
Dim Copyto As String
Application.DisplayAlerts = False
ThisWorkbook.SaveAs "My_POReq.xls"
Application.DisplayAlerts = True
'the If below is to attach copy of approved PO Req to originator - M43,44 and 45 hold approvals
If Len(Range("M43")) > 3 Or Len(Range("M44")) > 3 Or Len(Range("M45")) > 3 Then
Copyto = Range("M42") & ";"
Else
Copyto = ""
End If
OutMsg = "The attached PO Req is submitted for your approval or execution." & Chr(13) & _
"If you are an approver, please click on the button nearest to your position title." & Chr(13) & _
"Your login will be used to signify approval (if you approved it)." & Chr(13) & _
"Once approved, please e-mail to next approver or appropriate Purchasing contact"

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = Copyto
.BCC = ""
.Subject = "PO Requisition for Approval & Processing"
.Body = OutMsg
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Application.CommandBars("Standard").FindControl(ID:=3).Enabled = True
End Sub
 
Upvote 0
it sounds like you need to create a template and have this template out on the intranet instead of a changeable workbook

hope this helps
 
Upvote 0
Actually, the form is a template. It is a form to which users add input in series (that is, the template needs to save the users input, as it moves from one to the other). However, the original template needs to always be a blank form, so that users start out afresh.

I think this will be my weekend project at home... (sigh...)
 
Upvote 0
If you don't want your files to be overwritten this would give it a unique name with date and time values (i.e., so you can save versions of previous POReqs in case you need to look at an earlier workbook sent out):

Code: 
Dim strTemp
strTemp = ThisWorkbook.Path & "\" & "My_POReq_" & Format(Now, "yyyy-mm-dd_hhnnss") & ".xls"
ThisWorkbook.SaveAs strTemp

It seems you may have displayalerts backwards.
From the help files on displayalerts:
When using the SaveAs method for workbooks to overwrite an existing file, the 'Overwrite' alert has a default of 'No', while the 'Yes' response is selected by Excel when the DisplayAlerts property is set equal to True.
Click to expand...

AB
 
Upvote 0
Does it help if you add a complete path to the SaveAs statement?
 
Upvote 0
What about deleting the file if it exists?

On Error Resume Next
Kill "My_POReq.xls"
On Error GoTo 0
ThisWorkbook.SaveAs "My_POReq.xls"

Or using
ThisWorkbook.SaveCopyAs "My_POReq.xls"
 
Upvote 0
This is Saturday am... About to do my homework... and here I find some juicy tidbits from you guys, inspiring a bunch of courses of action. I am confident (nay, just hopeful) I'll get it right this time.
This community is tops in my estimation! Thank you all.
Chuck
PS. Please, do not consider this a sign off. If you have any more suggestions on this thing, by any means, tell me!
Thanks again.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Loop to get emails from Outlook 365 has broken, and I cant work out why !
Replies
3
Views
790
netrixuser
N
M
  • Question Question
Macro modification
Replies
1
Views
1K
DanteAmor
DanteAmor
A
  • Question Question
Problem With Scrolling Data Through A Static Display Range
Replies
5
Views
1K
Ark68
A
M
  • Question Question
Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed
Replies
3
Views
9K
Mike UK
M
C
  • Question Question
Ton of script in case anyone wants to review, maybe something useful to someone
Replies
0
Views
3K
cizzett
C

Forum statistics

Threads
1,224,541
Messages
6,179,418
Members
452,912
Latest member
alicemil

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