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    
post Dec 3 2017, 01:06 PM

Posts: 13
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
post Dec 3 2017, 02:32 PM

UA Admin
Posts: 33,794
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
post Dec 4 2017, 11:31 AM

Utter Access VIP
Posts: 6,231
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.

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
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 12:08 AM