Problem with e-mail composing and sending

L

liampog

Active Member
Joined
Aug 3, 2010
Messages
308
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I use the code below.

It opens a blank e-mail in Outlook, sets the To and Subject lines and then copies a Named Range on the worksheet and pastes it into the e-mail body.

It then sends the e-mail.

I have 3 computers within my workplace where this code works (all Windows 10 terminals running the same versions of Outlook and Excel).

There is a 4th computer where the code doesn't work. It's exactly the same setup as the other 3.

It does everything apart from paste the range to the e-mail and ends up just sending a completely blank e-mail.

Any ideas what's wrong and why this 1 computer doesn't work?

Are there any other methods? I'm aware of the MailEnvelope option but when I first set out designing this sheet, that method didn't seem to work properly.


Thanks
Liam

Code: 
Sub Report_Send()
    
    Response = MsgBox("Is the report ready to send?", vbYesNo, "Send Report")
    
    If Response = vbNo Then Exit Sub
    
    ThisWorkbook.Save

    With Application

        .ScreenUpdating = False

        .EnableEvents = False

        .DisplayAlerts = False

    End With
    
    Dim OutlookApp, Report_MailObject As Object
    
    Set OutlookApp = CreateObject("Outlook.Application")
    
    Set Report_MailObject = CreateObject("Outlook.Application").CreateItem(olMailItem)
    
    With Report_MailObject
        
        .To = Range("MailingGroup").Value

        .Subject = "REPORT  |  " & UCase(Format(Range("Date").Value, "ddd dd/mm/yyyy")))
        
        End If
        
        .Display
    
    End With
    
    Range("MailRange").Copy
    
    Wait 2

    SendKeys "^({v})", True
    
    Wait 2
    
    Report_MailObject.Send
    
    Set Report_MailObject = Nothing
    
    Application.CutCopyMode = False

    With Application

        .DisplayAlerts = True

        .ScreenUpdating = True

        .EnableEvents = True

    End With
    
    ThisWorkbook.Close True
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi Dante

Thanks for the reply.

I’ve tried this method in the past too but the formatting of the HTML isn’t always perfect.

Does anyone have an idea as to why it’s not pasting into the body of the email like the other 3 computers are?

Thanks
Liam
 
Upvote 0
Using the instruction to paste excel information into another application is very unstable, so in your code you have the wait instructions, to wait for the other application to respond and then be able to paste.
Try increasing the time from Wait 2 to Wait 7 for example.

VBA Code: 
    Wait 2
    SendKeys "^({v})", True
    Wait 2
_______________________________________________________________________________
By the way, Your code is not complete, you have this and it is not correct, maybe it didn't paste full.:

VBA Code: 
    .Subject = "REPORT  |  " & UCase(Format(Range("Date").Value, "ddd dd/mm/yyyy"))[B][COLOR=rgb(226, 80, 65)])[/COLOR][/B]      
        [B][COLOR=rgb(226, 80, 65)]End If[/COLOR][/B]
_______________________________________________________________________________

Another option is to create a table also in HTML but less complex than the example in post #2.
Try this:

VBA Code: 
Sub Report_Send()
    Dim OutlookApp, Report_MailObject As Object
    Dim r As Range, hTable As String, i As Long, j As Long
    
    Set OutlookApp = CreateObject("Outlook.Application")
    Set Report_MailObject = CreateObject("Outlook.Application").CreateItem(0)
    
    Set r = Range("MailRange")
    hTable = "<table border><tr>"
    For i = 1 To r.Rows.Count
        For j = 1 To r.Columns.Count
            hTable = hTable & "<td>" & r.Cells(i, j) & "</td>"
        Next
        hTable = hTable & "</tr>"
    Next
    hTable = hTable & "</table>"
    
    With Report_MailObject
      .To = Range("MailingGroup").Value
      .Subject = "REPORT  |  " & UCase(Format(Range("Date").Value, "ddd dd/mm/yyyy"))
      .HTMLBody = "<HTML> <BODY>" & "<P>" & hTable & "</P>" & "</BODY> </HTML>"
      .Display
    End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
E-mail Sending Loop
Replies
5
Views
371
mumps
mumps
T
  • Question
Send automated e-mails from Excel to Outlook
Replies
2
Views
140
tiredofit
T
E
  • Question
Trying to use a month name in my VLookup
Replies
2
Views
169
Eric Penfold
E
J
  • Question
Excel Email Lock up
Replies
3
Views
221
Logit
Logit
E
  • Question
VBA + Outlook
Replies
1
Views
205
Edson Santos 294
E

Forum statistics

Threads
1,215,746
Messages
6,126,638
Members
449,325
Latest member
Hardey6ix

Share this page

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