Help With Creating Outlook Messages

a68tbird

New Member
Joined
Nov 15, 2011
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Hello All -
I have been using a script to send two different emails generated from the same information on my spreadsheet. The VBA code that I have has been working perfectly until just recently (maybe couple of weeks ago). Now, the second email that gets generated is....not onFocus I guess is the best way to describe it. The window opens, and all of the information that is supposed to be in the email (To, Subject, Body, Attachment) are all there but I can't click on anything. There's just no response from any mouse action. Has anyone come across this ism before? I've made sure that I'm up to date with latest versions.

Here is the pertinent part of the code that relates to creating the emails:

Code:
'Set up outlook
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
     'Create message for PO approval
    On Error Resume Next
    With OutMail
        .To = "redacted@email.com" 'Insert required address here ########
        .CC = ActiveSheet.Range("C19") & "; " & "redacted2@email.com"
        .BCC = ""
        .Subject = "PO Approval Awaiting - " & ActiveSheet.Range("G8") & " RE: " & ActiveSheet.Range("G9")
        .HTMLBody = "Please see details of PO below: 
" & RangetoHTML(rng) & "
Property: " & ActiveSheet.Range("G9") & "
Vendor: " & ActiveSheet.Range("C8") & "
Total: " & ActiveSheet.Range("POTotal").Text & ActiveSheet.Range("G19") 
        
        '.Attachments.Add strPath & strFName
        .Display   'Use only during debugging ###########
        '.Send      'Uncomment to send e-mail ##########
    End With
     
    With Application
    .EnableEvents = True
    .ScreenUpdating = True
    End With
    
    'Set FirstName
            FullName = ActiveSheet.Range("C9")
            SpacePos = InStr(FullName, " ")
            FirstName = Left(FullName, SpacePos - 1)
       
            
    With Application
    .EnableEvents = False
    .ScreenUpdating = False
    End With
    
    
    'Set up outlook second instance
     Set OutMail2 = OutApp.CreateItem(0)
     'Create message for label order
    On Error Resume Next
    With OutMail2
        .To = ActiveSheet.Range("C13") 'Insert required address here ########
        .CC = ActiveSheet.Range("C19") & "; "
        .BCC = ""
        .Subject = ActiveSheet.Range("G9") & " " & "PO Attached - " & DateTime.Date
        .Body = "Hi " & FirstName & vbCr & Application.InputBox("Enter message for vendor", , "") & _
        vbCr & vbCr & "Thanks"
        .Attachments.Add strPath & strFName
        .Display   'Use only during debugging ##############################
        '.Send      'Uncomment to send e-mail ##############################
    End With
    
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutMail2 = Nothing
    Set OutApp = Nothing
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top