+ Reply to Thread
Results 1 to 4 of 4

Clear a range of cells on spread sheet after sending workbook in an email

  1. #1
    Registered User
    Join Date
    11-27-2012
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2007
    Posts
    81

    Clear a range of cells on spread sheet after sending workbook in an email

    I have spread sheet that employees fill in info and they click a button that attaches and sends the workbook to an email address.
    I'm want the entries in the spread sheet that they filled out to clear or be removed when I click the same button that sends the email.

    The following VBA code works great to display the email filled in with "TO:, Subject:, Attachment, and a message in the Body of the email. All the employee needs to do is click the send button, which I could remove and have it send automatically.

    But my issue is after the email is sent i was hoping in the same code I could make a range of cells (A10 thru G31) clear or remove what the employee entered so its back to a blank form.

    This is the code for sending the email with attachment, having trouble figuring this out.

    Sub SendMail()

    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then
    Set olApp = CreateObject("Outlook.Application")
    Err.Clear
    End If
    On Error GoTo 0

    Set olMail = olApp.CreateItem(olMailItem)

    With olMail
    .Subject = "Please Enter Consignment Transactions ASAP"
    .To = Range("AA1")
    .CC = Range("AB1")
    .Attachments.Add ThisWorkbook.FullName
    .Body = "Please Enter Consignment Transactions ASAP"
    .Display
    End With
    End Sub

  2. #2
    Valued Forum Contributor xlbiznes's Avatar
    Join Date
    02-22-2013
    Location
    Bahrain
    MS-Off Ver
    Excel 2007
    Posts
    1,223

    Re: Clear a range of cells on spread sheet after sending workbook in an email

    Hi,

    Just add this line to clear the data.

    Please Login or Register  to view this content.
    Happy Computing ,

    Xlbiznes.

    To show your appreciation please click *

  3. #3
    Forum Contributor
    Join Date
    12-31-2012
    Location
    Jhang, Pakistan
    MS-Off Ver
    Excel 2010
    Posts
    250

    Re: Clear a range of cells on spread sheet after sending workbook in an email

    What is the difficulty in clearing the range???
    Just use this line of code at the end:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    11-27-2012
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2007
    Posts
    81

    Re: Clear a range of cells on spread sheet after sending workbook in an email

    Thanks! this worked. I tried this earlier but must have been entering it in the wrong place in the code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1