+ Reply to Thread
Results 1 to 4 of 4

Macro not Auto-Sending Emails

  1. #1
    Registered User
    Join Date
    01-21-2014
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    2

    Macro not Auto-Sending Emails

    I'm very new to the VBA world and could use a bit of help. I build a macro (probably not very efficient) that sends an email if column "N" is "yes". It worked perfectly this morning and for some reason, I can't get it to play nice. Could someone much smarter than I please take a look and let me know if I'm overlooking something?

    Dim OutApp As Object
    Dim OutMail As Object
    Dim Tocell As Range
    Dim CCCell As Range
    Dim SigString As String
    Dim Signature As String
    Dim myRange As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    If Dir(SigString) <> "" Then
    Else
    Signature = ""
    End If

    On Error GoTo cleanup
    Set myRange = ActiveSheet.Columns("K:K")
    For Each Tocell In Columns("K").Cells.SpecialCells(xlCellTypeConstants)
    For Each CCCell In Columns("P").Cells.SpecialCells(xlCellTypeConstants)
    If Tocell.Value Like "?*@?*.?*" And _
    LCase(Cells(Tocell.Row, "N").Value) = "yes" Then
    If CCCell.Value Like "?*@?*.?*" And _
    LCase(Cells(CCCell.Row, "N").Value) = "yes" Then
    Set OutMail = OutApp.CreateItem(0)
    SendKeys "^[ENTER}"
    With OutMail
    .to = Tocell.Value
    .cc = CCCell.Value
    .Subject = Cells(Tocell.Row, "G").Value
    .Body = "Hello, " & vbNewLine & vbNewLine & _

    .Send

    End With
    On Error GoTo 0
    Set OutMail = Nothing
    End If
    End If
    Next CCCell
    Next Tocell


    cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True

    End Sub

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,071

    Re: Macro not Auto-Sending Emails

    Hi remadison3,

    I noticed first up that you have different brackets around the word 'ENTER'.

    Also you need quotation marks at the end of the .Body part & remove the underscore unless you want to keep writing code on the next line.

    Can you advise at what line you get an error & what the error is?

    Thanks
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    01-21-2014
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Macro not Auto-Sending Emails

    Ooo, sorry about that. That was the result of a hasty edit.

    The weird thing is that I'm not getting an error message from excel or outlook.

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,071

    Re: Macro not Auto-Sending Emails

    Hi remadison3,

    Try the attached file,

    I've created some dummy data & added values (Yes/No) in Column N.

    This will display an email for each email address in Column A if the Value in Column N = Yes.

    You will need to comment in (remove the apostrophe) in front of the .Send part & comment out (add an apostrophe) in front of the .Display if you want to send the e-mails.

    Check it works first though.

    Cheers
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sending Emails via Macro
    By jnoble3 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2013, 03:34 PM
  2. Sending emails through macro
    By nbhatia4u in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-12-2013, 08:01 AM
  3. [SOLVED] HELP! Macro not sending emails
    By g1987 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2012, 12:25 PM
  4. excel macro for sending emails
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 12-11-2009, 08:50 AM
  5. Sending macro emails using excel: Send emails with their passwords.
    By loveisblind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2009, 03:16 PM

Tags for this Thread

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