Check out some similar questions!

rsdjimbie · Jul 27, 2012, 10:20 AM

Hi, I have this VBA code running fine but I cannot get rid of the email notification when running the code, I have tried varius line of code like "Application.ScreenUpdate=False" and Application.DisplayAllerts.False" but with no joy. Please have a look at my attachment called "Email notification".
Thanks to a great site and people helping us newbies!

Chic_Bowdrie · Jul 27, 2012, 12:50 PM

Originally Posted by rsdjimbie

Hi, I have this VBA code running fine but I cannot get rid of the email notification when running the code, I have tried varius line of code like "Application.ScreenUpdate=False" and Application.DisplayAllerts.False" but with no joy. Please have a look at my attachment called "Email notification".
Thanks to a great site and people helping us newbies!

Where is the attachment?

rsdjimbie · Jul 27, 2012, 10:03 PM

The attachment.

Chic_Bowdrie · Jul 28, 2012, 03:14 PM

The problem with this spreadsheet seems much greater than avoiding sending an email. I wasted 20 or so pages for all the entries in the A column of sheet1 and the only difference on any of the pages was what appeared in cell A3 on the paystub. Until you figure out what else you want to do with the macro program, I would delete entries below cell A9 on sheet1. That will limit you to printing 3 pages at a time until you finish whatever you're doing.

Now to address what the macro is doing. After the " 'Lena " comment in Module2 of the VB-script code, you have Case "A2" followed by a comment. The program looks at the cells in column A starting with cell A7. Because there is an "A2" there (in cell A7), the program will execute the email subroutine for that entry. Also, if "A2" happened to be in any other cell in column A below cell A7, it too would prompt another email. To prevent the email prompt, put a comma in front of ".SendMail Recipients:...." and "Subject :="Sal Advise... " or just delete those lines. The script will continue to print a page for every other non-blank entry in the column that is not " or just delete those lines. The script will continue to print a page for every other non-blank entry in the column that is not ".

If this doesn't help, you need to make it very clear what your overall goal is.

rsdjimbie · Jul 29, 2012, 02:16 AM

Thanks Chic_Bowdrie for your response!
Sorry for waisting your papar, my mistake, I have "commented" the printing line so it will NOT print again.

This macro do two things 1) Prints a pay stub and 2) If the cell value is a certain value, in this new example "Email" on the sheet "PaySlip" cell "A3", will it instead of printing a pay stub, email the pay stub, this beacause some employees have email adresses (saving paper-sorry again for wasting yours).

Your concern regarding col "A" on Sheet1 repeating the same value will not happen as these numbers are unique to the worker. To illiminate confusion have I changed the those values to "Email" and "Don't" With that out of the way, let me talk you through the macro.

In this example I only have one instance where it sends a email, I have about 15 workers that will have there's mailed, therefore having to sit and wait for each email notification to count down is driving me out the walls.

It starts with creating a count +1 range in col "AI" sheets1.

Code:

Sheets("Sheet1").Range("AI7:AI" & lastrow).FormulaR1C1 = "=IF(RC1="""",R[-1]C,R[-1]C+1)"

Then it executes a loop using the MaxNum function on the Sheet "PaySlip" for range "A3".

Code:

MaxNum = WorksheetFunction.Max(RngNum)
    For i = 1 To MaxNum Step 1   'change maxnum to some small number to test short runs
        Range("A3").Value = WorksheetFunction.Index(RngClk, WorksheetFunction.Match(i, RngNum, 0))

Whilst this loop is running will it annalise the value of cell "A3" of sheet "PaySlip" and calculate if it must print or email the pay stub using the " Case" statement.

Code:

'Print this sheet only if cell A3 don't have email address
  Select Case Range("A3").Value
 
   'Lena
   Case "Email" 'Then email
   ThisWorkbook.Sheets("PaySlip").Copy
    With ActiveWorkbook
    .SendMail Recipients:="[email protected]", _
    Subject:="Sal Advise " & Format(Date, "dd/mmm/yy")
    .Close SaveChanges:=False
    End With
   
    
     Case Else
'      ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
       End Select
    Next i

If "Case" = "Email" in sheets "PaySlip" cell "A3" then will it email sheet, Case Else will it print the sheet.

Please run the code and grind tour teeth at the mail notification.

I have added a simpler attachment with the same code.
Thanks again!

Chic_Bowdrie · Jul 29, 2012, 03:53 AM

So, the problem is not that the macro sends an email, but that it requires confirmation instead of sending the email automatically? Obvious to you, but not me. Please acknowledge if that is the problem to solve.

The send email confirmation is generated by your email service, not your VB-script macro. If you have Outlook Express, for example, go to Options, Security, and uncheck "Warn me when other applications try to send mail as me." Click Apply and you should be good to go.

rsdjimbie · Jul 29, 2012, 12:43 PM

Yes that is the problem to solve, I need the mail to be send automatically. I am using MS Office 2007's Outlook email.

Chic_Bowdrie · Jul 30, 2012, 02:15 AM

Did you get Outlook to send emails automatically?

rsdjimbie · Jul 30, 2012, 02:21 AM

No, still receive the warning pop up, I have also deactivated this in the "trust center" of Outlook.

JBeaucaire · Jul 30, 2012, 05:40 PM

I spent 6 months once picking at that problem and gave up. It's a security warning for a reason, Outlook is sending email at the behest of a nonhuman, and it doesn't like that.

I don't do Outlook programming, but from what I hear macros written to run from inside Outlook do not suffer this same problem, so if you can get Outlook to control Excel instead of the other way around, you may be in business.

It was a black hole of time for me, I gave up and just press the OK button.

rsdjimbie · Jul 30, 2012, 11:10 PM

QUOTE=;][/QUOTE]
Thank you for having a look at my problem JB and Chic!

Jb, you don't have to give up, I found this very useful website by Ron de Bruin where he solved exactly this problem. Have a look at what he did and there is no more email warning pop ups. I used his code and all is working fine as explained by him.Please follow this link- Example Code for sending mail from Excel

rsdjimbie · Jul 30, 2012, 11:12 PM

Thank you for having a look at my problem JB and Chic!

Jb, you don't have to give up, I found this very useful website by Ron de Bruin where he solved exactly this problem. Have a look at what he did and there is no more email warning pop ups. I used his code and all is working fine as explained by him.Please follow this link- Example Code for sending mail from Excel

Chic_Bowdrie · Aug 1, 2012, 05:14 PM

Good job, rsdjimbie!

Meanwhile I found much discussion of this problem by googling "Item.Send outlook disable." One link offered a program called Express ClickYes which automatically... wait for it... clicks yes for you. Of course, Ron de Bruin's method is a much cleaner way to go. Thanks for the resource you found and the chance to learn with you.

I tried to "Helpful!" you again, but Ask Me's code wouldn't allow it.