none
When sending email from Excel using VBA, compose window appears first time each day RRS feed

  • Question

  • Hello. I recently wrote a macro for excel using VBA which is intended to send an email, the subject and body of which contain contents of the current cell. It is designed to create the email and send it without any interaction from me beyond confirming that I do want to send it. However, the first time each day (my computer is rebooted each night) I use it, it opens a compose window with a recipient and subject already filled out. This compose window contains information from a saved message I keep on my desktop (used to clock in and out at work). I've searched for a solution online, and haven't been able to find a similar problem. I was hoping someone could help me figure out how to stop this. It isn't crashing the program, but it's quite annoying to have to close an unneeded window every day. Below, I've included a (modified for privacy) version of the code I used to create and send the email.

    Sub SendEmail()
    If Not Intersect(ActiveCell, Range("A20:A84")) Is Nothing And Not IsEmpty(ActiveCell) Then
        Dim wrnum As String
        Dim iret As Integer
        Dim subj As String
            subj = "Please log out Job#"
        Dim olApp As Outlook.Application
            Set olApp = CreateObject("Outlook.Application")
        Dim olMail As Outlook.MailItem
            Set olMail = olApp.CreateItem(olMailItem)
        
        'Sets the subject/body of the email
        subj = subj + CStr(ActiveCell.Value)
        
        iret = MsgBox("To: " + "person@domain.com" + vbNewLine + "Subject: " + subj, vbYesNo, "Confirm Email")
        
        If iret = vbNo Then
            ManualWRNum.Show
            If manualnum = "null" Then
                iret = 0
            Else
                subj = "Please log out Job#" + manualnum
                olMail.To = "person@domain.com"
                olMail.Subject = subj
                olMail.body = subj
                olMail.Send
            End If
        Else
            'Composes & sends the email
            olMail.To = "person@domain.com"
            olMail.Subject = subj
            olMail.body = subj
            olMail.Send
        End If
    Else
        Dim col As Long
        col = Selection.Interior.ColorIndex
        If Selection.Interior.ColorIndex = xlNone Then
            Selection.Interior.ColorIndex = 3
        End If
        Selection.Interior.ColorIndex = 3
        Sleep (200)
        Selection.Interior.ColorIndex = col
        Sleep (100)
        Selection.Interior.ColorIndex = 3
        Sleep (200)
        Selection.Interior.ColorIndex = col
        Sleep (100)
        Selection.Interior.ColorIndex = 3
        Sleep (200)
        Selection.Interior.ColorIndex = col
    End If
    
    End Sub

    The overall IF statement is to verify I have the correct cell selected, and the code following the final ELSE simply causes the selected cell to flash red if this isn't the case. It doesn't (as far as I know) affect the actual function of the emailing code.

    Wednesday, September 10, 2014 5:58 PM

All replies

  • Would you be z more specific?

    I did't know you have problem with auto-run your solution (file, code) or auto run Excel application?

    If I'm right, then you can use task scheduler to run File.xlsm with Worksbook_open procedure or send prepared Email:


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Friday, September 12, 2014 10:44 AM
    Answerer
  • I'm not sure what you mean by more specific. I created the code as a module within excel, using the built-in IDE. The only trouble I'm having is that, the first time I run the subroutine I posted each day, it not only prepares and sends the email as it was designed to do, but opens the last email template I used in one of Outlook's Compose windows, as if I'd double-clicked the template.
    Saturday, September 13, 2014 5:07 PM
  • If you want to use template, you should save it on your drive an change this line:

    Set olMail = olApp.CreateItem(olMailItem)

    to:

    Set olMail = olApp.CreateItemFromTemplate("C:\Temp\myTemplate.oft")



    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Monday, September 22, 2014 8:50 AM
    Answerer
  • No, I'm not trying to use the template. My VBA code puts all the necessary information in the email already.

    The problem is that when I run the subroutine, it also opens a compose window from whatever the last template I used was, but only the first time each day/restart. If you look, the code composes and sends the email with the only interaction from me being clicking "Yes" to the confirm box (Or by clicking no, then filling out the job number in a simple input box named ManualWRNum). That's what I want: To run the subroutine, click "yes" and be done. Instead, the first time each day, I must run the sub, click "yes", then close the compose window that popped up. I just want to know why this is opening the compose window without being instructed to, and what I can do to stop it.


    • Edited by Darin Rodberg Tuesday, September 23, 2014 2:01 PM clarification
    Tuesday, September 23, 2014 12:38 PM
  • You are calling form and do something (not describable)

    ManualWRNum.Show
            If manualnum = "null" Then

    You'r use manualnum variable (i think global one) and check status

    There is not any loop, everything is dependent on the ActiveCell.value

    I do not know what is it for (on the same selection field, sleep assign and sleep again ...) ?

    Dim col As Long
        col = Selection.Interior.ColorIndex
        If Selection.Interior.ColorIndex = xlNone Then
            Selection.Interior.ColorIndex = 3
        End If
        Selection.Interior.ColorIndex = 3
        Sleep (200)
        Selection.Interior.ColorIndex = col
        Sleep (100)
        Selection.Interior.ColorIndex = 3
        Sleep (200)
        Selection.Interior.ColorIndex = col
        Sleep (100)
        Selection.Interior.ColorIndex = 3
        Sleep (200)
        Selection.Interior.ColorIndex = col
    End If


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Wednesday, October 1, 2014 3:01 PM
    Answerer
  • You are calling form and do something (not describable)

    You'r use manualnum variable (i think global one) and check status

    There is not any loop, everything is dependent on the ActiveCell.value

    I do not know what is it for (on the same selection field, sleep assign and sleep again ...) ?

    The manualnum variable is used only if I click "no" in the confirmation box, then fill out the job number in another box, as I explained in my last post:

    (Or by clicking no, then filling out the job number in a simple input box named ManualWRNum)

    The second block of code you quoted is outside of the block in which the email code runs, as I explained in my first post:

    The overall IF statement is to verify I have the correct cell selected, and the code following the final ELSE simply causes the selected cell to flash red if this isn't the case. It doesn't (as far as I know) affect the actual function of the emailing code.

    I have tried removing the overall IF statement and just running the code without it. The same problem occurs.

    Monday, October 6, 2014 1:37 PM