UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Automatically Email Worksheet, Office 2007    
 
   
Fenderrick
post Dec 3 2017, 01:06 PM
Post#1



Posts: 2
Joined: 21-September 16



Hi, Please can you help me out here, i've been trying all day and getting nowhere. I have a spreadsheet and i want to automatically send an email of the sheet, when any cell in column AG has the text "yes" in it.
Go to the top of the page
 
GroverParkGeorge
post Dec 3 2017, 02:32 PM
Post#2


UA Admin
Posts: 31,250
Joined: 20-June 02
From: Newcastle, WA


The best way I know get a function started in Excel is to record a macro of the steps you need to take to accomplish the task. Then you can edit and modify the resulting VBA to get it exactly the way you want it. This has the added advantage of giving you a chance to learn more about how Excel VBA works for future tasks that are similar.

Why not start with that approach here? Record a macro of yourself manually emailing the spreadsheet. Edit the result as needed to make it automatically happen when some types in the letters "yes" in that column, AG.
This post has been edited by GroverParkGeorge: Dec 3 2017, 02:33 PM

--------------------
Go to the top of the page
 
dflak
post Dec 4 2017, 11:31 AM
Post#3


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


The following is my "standard" module for emaling something fromExcel. You pass it the addresses for To, CC and BCC, Subject, Body and Attachment.

To pass the current workbook as an attachment first do ThisWorkbook.save and then use ThisWorkbook.Path & "\" & ThisWorkbook.Name as the attachment name.

You have to be logged on for this to work. Outlook must be installed and configured, but does not have to be running.

CODE
Sub Mail_Workbook(ToString As String, SubjectString As String, BodyString As String, _
    Optional CCString As String, Optional BCCString As String, Optional AttachmentName As String)
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
   ' Change the mail address and subject in the macro before you run it.
    With OutMail
        .To = ToString
        If CCString <> "" Then
            .CC = CCString
        End If
        If BCCString <> "" Then
            .BCC = BCCString
        End If
        .Subject = SubjectString
        .Body = BodyString
        If AttachmentName <> "" Then
            .Attachments.Add (AttachmentName)
        End If
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 09:52 AM