Auto Send Email from Excel 2007 No Error when Outlook 2007 is full

I have a macro that automatically sends email from excel 2007 through
outlook 2007.  The problem is that if the user's outlook inbox is at
the size limit it will not send the email.  When we were on Office
2003 my error handler worked to let the user know that the email
wasn't sent, but now on office 2007 it just doesn't send and doesn't
let the user know that the email did not go.  The code also would
recognize an error if the email address was invalid in 2003, but that
does not work in 2007 either: just the email is not sent.  My code is
below (most of it was from Ron de Bruin's code).  Can anyone offer
some suggestions on this?  Thanks

Dim iResponse As Integer
iResponse = MsgBox("Are you sure you want to send to current
customer?", vbYesNo, "Email All Customers")
If iResponse = vbYes Then


        Application.DisplayAlerts = False
    Sheets("Errors").Select
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Sheets.Add.Name = "Errors"
    Sheets("Errors").Select
    Sheets("Errors").Move After:=Sheets("Work")
    Sheets("Errors").Select
    ActiveCell.FormulaR1C1 = "Emails not Sent to Customers"
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Columns("A:A").EntireColumn.AutoFit
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(C[-1])"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]>1"
    Sheets("New Communication Template").Select
    Range("D4").Select

    Application.Run ("FilterCustomer")

    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Name = ("Temp")
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False
    Range("D4").Select
    Selection.Validation.Delete
    ActiveSheet.Shapes("Button 1").Select
    Selection.Cut
    ActiveCell.SpecialCells(xlLastCell).Select
    Selection.Offset(2, -5).Select
'    Application.Goto Reference:="Disclaimer"
'    Application.CutCopyMode = False
'    Selection.Copy
'    Sheets("Temp").Select
'    ActiveSheet.Paste
    Range("A4").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True

   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
    On Error GoTo ErrHandler
      .Item.To = " "
      .Item.CC = " "
      .Item.Bcc = " "
      .Item.Subject = " "
      .Item.To = ActiveSheet.Range("B1")
      .Item.Subject = ActiveSheet.Range("B2")
      .Introduction = ActiveSheet.Range("B3")
      .Item.Send
    End With


    ' Hide the envelope on the ActiveWorkbook.
    ActiveWorkbook.EnvelopeVisible = False
    Application.DisplayAlerts = False
    Sheets("Temp").Delete
    Sheets("New Communication Template").Select
    Range("D4").Select
    Application.DisplayAlerts = True



    Exit Sub

ErrHandler:
    ActiveWorkbook.EnvelopeVisible = False
    Application.DisplayAlerts = False
    Sheets("Temp").Delete
    Sheets("New Communication Template").Select
    Selection.AutoFilter
    Range("D4").Select
    Application.DisplayAlerts = True
    Sheets("New Communication Template").Select
    Range("D4").Select
    Selection.Copy
    Sheets("Errors").Select
    Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
        :=False, Transpose:=False
Application.CutCopyMode = False
Sheets("New Communication Template").Select
Range("D4").Select

Sheets("Errors").Select
    Range("A1").Select
    If Range("C1").Value = False Then
        Sheets("New Communication Template").Select
        Range("D4").Select
    Else
        Sheets("New Communication Template").Select
        Range("D4").Select
MsgBox "Some emails not sent to customers - check errors tab!  Verify
email addresses for customer on GroupMatrix tab.", vbOKOnly +
vbExclamation, "Email Send Error!"
        If vbOK Then
            Sheets("Errors").Select
            Range("A1").Select
        Else
        End If
    End If

  Else
  End If

End Sub '

0
bige3218 (1)
7/12/2011 3:29:50 PM
excel 39879 articles. 2 followers. Follow

0 Replies
501 Views

Similar Articles

[PageSpeed] 16

Reply:

Similar Artilces:

how do i can send a information of cell automaticaly via email?
I have generated a calibration file in excel by using conditional formating,here the help I need is,in a perticular cell where the conditional formating is done needs to be send as email alert to the mail receipents automatically if the condition of thet cell is "false" Rajani, see if this will help, http://www.rondebruin.nl/mail/change.htm http://www.rondebruin.nl/sendmail.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 &...

Outlook does not save the e-mail addresses entered
I am using Outlook 2000 SR1. When entering a new contact, it does not save the e-mail address along with the other information logged in. I have tried substituting the contact's name with the e-mail address to send e-mail, but I get an error message that it does not recognize the contact name. How can I fix this? ...

Unable to send to a couple different domains
I have Exchange Server 2003 running on a 2000 Server. Recenly we have been getting bounced messages from 3 different domains. The message reads "The destination server for this recipient could not be found in Domain Name Service (DNS). Please verify the email address and retry. If that fails, contact your administrator.<8th-jd-cc.comm_corr #5.4.0>" I have tried changing the DNS entries under the SMTP virtual server. Searching on Microsofts site I find this possible problem "The SMTP connector is configured to forward e-mail messages to a smart host, and the SMTP ...

Auto Filter problem
I am using the auto filter for a particular column to sort out differen medical programs. When I click the drop down and click a program, i doesnt show me ALL the lines that say this particular program. Why is this? -- Message posted from http://www.ExcelForum.com Hi maybe some hidden characters in these lines 8e.g. additional space characters, etc.) -- Regards Frank Kabel Frankfurt, Germany "jkb724 >" <<jkb724.19rasx@excelforum-nospam.com> schrieb im Newsbeitrag news:jkb724.19rasx@excelforum-nospam.com... > I am using the auto filter for a particular column to so...

Exporting Excel Charts in Separate Tabs
I have an excel file with different charts on each tab. The Excel file has at least 20 tabs and I would like to export them into Word as a separate sheet per tab for a reference guide. Is there a way to export from excel without having to cut and paste each tab? I am using MS Office 2003. ...

unable to see messages in outlook 2003
after setting up outlook on vista machine i am unable to see messages in inbox or any of the personal folders, despite successful download from mail server. have also checked filters. i had plenty of issues during setting up outlook with 2007 version & with setting up mail data folders, but it finally worked. any assistance will be appreciated. -- mahim http://forums.slipstick.com "mahim" <mahim.45jx5y@invalid> wrote in message news:mahim.45jx5y@invalid... > after setting up outlook on vista machine i am unable to see messages in > inbox o...

Send campaign e-mails from a queue
Direct e-mail allows e-mails to be sent from a queue using the e-mail address given by the queue. However, I need an unsubscribe option so I am trying to do the same using either a quick campaign or full campaign e-mail activity. However, the system only allows me to send the email either from myself or the owner of the contact record and not from a queue. Is this normal or am I doin something wrong. TIA This is normal. You can use a "test" user account to send out the campaign, or update your CRM user profile's email value. Good luck. Frank Lee, Microsoft CRM MVP Wo...

Auto-Editing
Word 2003 has a very annoying default. It assumes too much! If I reformat a few words in a document, it changes the entire document to that formatting. Grr-rrr-r! I have to constantly press undo to get what I want. I know there's a feature that's causing this annoyance but I can't find it to turn it off! Can anyone help, please? See http://word.mvps.org/faqs/formatting/wholedocumentreformatted.htm. -- Stefan Blom Microsoft Word MVP "Connie Martin" <ConnieMartin@discussions.microsoft.com> wrote in message news:CD959D82-F81B-4A9D-993E-73...

Delegates Sending 'On behalf of'
I have recently set the permission for an delegate to send messages on my behalf, and she has been set as an Author to my mailbox. However, when she accesses my Inbox and tries to send on my behalf the recipients do not see 'sent on behalf of' on the message. Also, if she adds the 'from' field to the message view my name is not automatically populated. How can we set it so that when she is sending as me that the from field is filled in? Please, people, post your VERSION of Outlook!!!! Is she directly opening your mailbox or is this a second mailbox that she opens ...

Auto-Calculate
I have a spreadsheet that utilize the stock quote add-in from MSN. On a daily basis I refresh the add-in functions for the Highs, Lows, Last, Volume, etc. On occasion the spreadsheet reverts to manual caculation settings, and I have to press the F9 even after I updated the quotes. I've always preferred the any spreadsheets I open, blank or otherwise, the automatic setting selected for calculations. I consider this an annoyance. Has anyone experienced this glitch? Is there a fix for it? Ron_D Ron Excel takes the calculation mode from the first workbook that opens in a session. Sub...

MS CRM 3.0 + Outlook
Hi,I installed MS CRM 3.0 customer for outlook but I found under small Microsoft CRM in outlook that Deleted items how one adds the module of the CRM under this menu. Thanck You. Hi Ahmed, You can drag a folder from 'the small microsoft crm menu' to the favourits (the upper left corner of outlook). This way you can easily access microsoft crm folders you frequently use! Hope this helps, Best regards, Merijn van Mourik http://crmerijn.blogspot.com "ahmed" wrote: > Hi,I installed MS CRM 3.0 customer for outlook but I found under small > Microsoft CRM in outloo...

How can I save Excel file to A CD
I need to save An excel file to A CD, so I can view and work with it on A laptop. I am useing office 2003 "Juleslib" <Juleslib@discussions.microsoft.com> wrote in message news:76E7A9BC-53AB-400C-AC00-8A3EA3B09CA5@microsoft.com... >I need to save An excel file to A CD, so I can view and work with it on A > laptop. > I am useing office 2003 Save it to your HDD and then use your burning software to put it onto a CD. However, a CD is 700 MB - that's an absolutely ENORMOUS file. You'd be better off with either a USB memory stick, or emailing it... You need...

Outlook database structure
Hello, Can someone tell me where I can find a description of the outloo database tables? I mean which fields, datatype, etc etc ... especially want the table in which the accounts are kept. thx ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com found it, if anyone needs it ... http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnout98/html/olexcoutlk.as ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View a...

Can Exchange administrators secretly read user's email without their knowlege?
This is a simple question and your help will appreciated. If I were an Exchange administrator, can I read a user's email without their knowlege? On 7 Jun 2006 06:44:37 -0700, "PizzaPete" <terryleemundy@comcast.net> wrote: >This is a simple question and your help will appreciated. If I were an >Exchange administrator, can I read a user's email without their >knowlege? Yes. With the proper permissions that arent there by default in E200x. http://support.microsoft.com/default.aspx?scid=kb;en-us;821897 "How to assign service account access to all mailboxe...

Publisher 2007 compatibility with Office 2003
I run Office Standard Edition 2003, which didn't include Publisher. I'd like to now download the free trial version of Publisher 2007, but came across a warning that it MIGHT negatively affect my 2003 Outlook. Is Publisher 2007 safe to download without screwing up my existing Office 2003 files? thanks. Yes it is - I did just that and Outlook has had no issues. I haven't tried to email straight from Publisher tho. Not because of any issues but because I have no need to do so. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dumm...

Can receive but not send emails
Task 'lrbutz@verizon.net - Sending' reported error (0x800CCC78) : 'Unable to send the message. Please verify the e-mail address in your account properties. The server responded: 530 5.7.0 No AUTH command has been given.' That is the error message I receive when trying to send a message from Outlook 2003, from a POP acct. Test messages at first were sent successfully, but now I only get the pop-up requiring me to repeatedly enter my user name and password, despite always re-checking the 'remember user name and password' box. This program worked yesterd...

Excel macro & MS XP OS trouble
I have recently switched from the MS NT operating system to the MS XP Professional os. My problem is I have an Excel macro that used to work with the NT system and now it does not with XP. Everytime I try to submit my Excel form I now get the following error message: "A document with the name '<name of file>' is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document either close the document that currently open, or renmae one of the documents." I have tried to rename the excel...

received a 551 error message when sending an email
-- Barbara R. Griffin Barbara@BarbaraRGriffin.com "Barbara R. Griffin" <BarbaraRGriffin@discussions.microsoft.com> wrote in message news:E6649DF7-CC94-4F8D-9339-2AB2BFDC243D@microsoft.com... > > -- > Barbara R. Griffin > Barbara@BarbaraRGriffin.com Please use this BIG WHITE SPACE to write your question. If you'd bothered to do ANY searching in this group you would have found a huge thread earlier today of which one post contained the following: " From AT&T: I apologize but we are undergoing a system maintenance. Would you pl...

Outlook sending errors.
I have recently got a program that helps my spam and junk=20 problem called Mail Washer (V4.1). The program works great but I am having a problem with=20 Outlook 2002. When I try to send a message I receive a=20 error quoting: Task 'mail.garethmyles.com - Sending and Receiving'=20 reported error (0x800CCC7D) : 'Your outgoing (SMTP)=20 server does not support SSL-secured connections. If SSL- secured connections have worked in the past, contact your=20 server administrator or Internet service provider (ISP). =20 The server responded: ?=1E?$for more information.' And Task &...

auto copy self
How do I arrange for Outlook to automatically copy myself on every new, reply or forward email? If you mean keep a copy of all sent, see settings under Tools/Options/Email Options and Advanced Options David "rich" <steamuk@aol.com> wrote in message news:055c01c3664f$dadecd30$a001280a@phx.gbl... > How do I arrange for Outlook to automatically copy myself > on every new, reply or forward email? ...

Exchange 2007 Catch-all Mailbox
In Exchange 2003 I had the ability to direct all undeliverable email to a users mailbox. I have not found out how to do this with 2007, is it even possible? ...

Any way to send messages on without all the recipients' names appeaing in it?
I want to send off received messages to various customers, but there should be no appearance of the other recipients names in the headers etc. Is there any way to do this? Desperate Peter Peter Jason wrote: > I want to send off received messages to various customers, but there > should be no appearance of the other recipients names in the headers > etc. > > Is there any way to do this? > > Desperate > Peter Bcc the recipients. No recipient gets to see the list of Bcc recipients. On Wed, 9 Mar 2011 19:53:59 -0600, VanguardLH <V@nguard.LH> wrote: >Peter...

When opening MS Outlook 2002 a window opens asking for network server?
Office XP Professional When I open MS Outlook a window opens up that is titled Microsoft LDAP Directory It asks for a server name, user name etc. This is a stand alone computer. Not on a network. Is there anyway of stopping this window from opening up each time I start Outlook? Thank You for your time Barry Barry, Your Outlook is configured to use an LDAP server for some reason. To remove the settings for an LDAP server, goto: 1) Tools 2) Email-Accounts Once in the Email-Accts window you will see four radio buttons(2 for E-mail and 2 for Directory) Click on the button next ...

Auto Scale Chart Macro -- Any improvements please?
This is the best way I've found to correctly scale a chart -- the only way I found to exclude a chart interpolating with #NA cells was to delete those cells containing #NA, and run the chart. This takes a long time on my computer. Wondering if there is a better way. This macro is directly taken from 2 excel mvps and modified; i believe a mehta and ron bovarty, but could be wrong. Thanks for taking a look. Sub AutoScaleYAxes() Dim ValuesArray(), SeriesValues As Variant Dim Ctr As Integer, TotCtr As Integer Application.Run "Extend_Stock_Data" Applic...

embed pictures in Outlook
Is it possible to embed pictures in outlook so the the recipient recieves then in email body and not in attachments line Thank you Yes. You need to set the message format to HTML and use the Insert > Picture menu choice*. /neo * Your mileage may vary as the steps listed above are for Outlook 98/2003 with Word being disabled as the e-mail editor. "belto" <belto@blueyonder.co.uk> wrote in message news:j2Rqg.40119$181.15574@fe3.news.blueyonder.co.uk... > Is it possible to embed pictures in outlook so the the recipient recieves > then in email body and not i...