Sending an Outlook email using Application.Sendkeys

[DDT~123]

Greetings All,

I have the below code which is coupled with a scheduled task and sends a report via email to a distribution. I'm needing a little help with two things...

(A) When the scheduled task starts the macro, my computer does odd things when the code is running. If my active screen is Internet Explorer, it'll double my home screens. If my active screen is Outlook, it'll page down and open the oldest email in my inbox. If composing an email, it'll tab several times and place the cursor at the end of the email after my signature. I'd like for there to not be any disruptions when the email is sending. I believe the problem lies within this code "Application.SendKeys "{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}" .Send"

(B) I'm looking to modify the code "With OutMail .To = Worksheets("Summary").Range("C17")" with an if statement which would look something like this "if value in B1 = "error", send email to the distribution list in C37, otherwise, send to the distribution list in C17"

Thank you in advance for your help!!

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

    On Error Resume Next
    
    strSubject = Worksheets("Summary").Range("B1")

    With OutMail
        .To = Worksheets("Summary").Range("C17")
        .CC = ""
        
                            .BCC = ""
        .Subject = strSubject

        .HTMLBody = RangetoHTML(rng)
        Application.SendKeys "{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}"
                                                .Send   '.Display

    End With
    On Error GoTo 0

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

 

[LockeGarmin]

(A) I'm guessing that send keys is supposed to add your signature to the end of the email. If so I would remove it completely and change the line above it to the following (If not what would be the harm in remove it anyway?):

.HTMLBody = RangetoHTML(rng) & .HTMLBody

(B) A couple of options:

Dim WS As Worksheet

Set WS = Worksheets("Summary")

.To = WS.Range(IIf(WS.[B1] = "error", "C17", "C37"))

Dim WS

Set WS = Worksheets("Summary")

If WS.[B1] = "error" Then
  .To = WS.[C17]
Else
  .To = WS.[C37]
End If

 

[DDT~123]

Thank you! It worked well...
Any idea why my num lock turns off when running this code? It did this before I modified the code, so it's not something with the code you provided.

 

[LockeGarmin]

I poked around a little and it looks like it's a bug with Send Keys. If you google around you'll find some work arounds. For me personally, I use a utility called AutoHotkey (http://ahkscript.org) for mapping several hotkeys and putting "SetNumLockState, AlwaysOn" near the top of my script ensures that Numlock stays on until the script closes. Not sure if that's applicable in your case but thought it might be helpful.