0

I'm working in Excel VBA to autosend mail every day.
When I manually run my code the email has all the information needed.
When I use task scheduler to open my workbook at a specific time and send the email, it will be missing my (graph & table) pasted range.

Edit: I commented On Error Resume Next and the error comes out at: (wordDoc.Range(Start:=wordDoc.Range.End - 1).PasteAndFormat wdChartPicture)

Sub Send_AutoMail()
    Dim rng1 As Range, r As Range
  
    Set r = Nothing
    'On Error Resume Next
    Sheets("maindata").Select
    Set r = Range("V4:AL78")
    r.Copy

    Set rng1 = Nothing
    'On Error Resume Next
    Sheets("LatestWeek").Select
    Range("R7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Set rng1 = Selection.SpecialCells(xlCellTypeVisible)

    Dim outlookApp As Outlook.Application
    Set outlookApp = CreateObject("Outlook.Application")
    Dim outMail As Outlook.MailItem
    Set outMail = outlookApp.CreateItem(olMailItem)

    outMail.Display
    Dim wordDoc As Word.Document
    Set wordDoc = outMail.GetInspector.WordEditor
    Dim shp As Object

    With outMail
        .Subject = "Testing[Daily]"
        .To = ""
        .HTMLBody = "<font style=font-family:Calibri>Hi All," & "<P>" & "TrendChart: PLT</font>" & "</P><P><br>"
        wordDoc.Range(Start:=wordDoc.Range.End - 1).PasteAndFormat wdChartPicture
        For Each shp In wordDoc.InlineShapes
            shp.ScaleHeight = 100
            shp.ScaleWidth = 100
        Next
        .HTMLBody = .HTMLBody & "</P><br>" & "<b><font style=font-family:Calibri color='red'>LOH</b></font>" & "</P>" & "<br><br>"
        rng1.Copy
        wordDoc.Range(Start:=wordDoc.Range.End - 1).PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
        wordDoc.Tables(1).Borders.OutsideLineStyle = wdLineStyleSingle
        wordDoc.Tables(1).Borders.OutsideLineWidth = wdLineWidth225pt
        wordDoc.Tables(1).Borders.OutsideColor = wdColorGray25
        .HTMLBody = .HTMLBody & "<font style=font-family:Calibri>Regards," 
    End With
         
   'outMail.Send

End Sub
Community
  • 1
  • 1
Shining Star
  • 31
  • 5
  • 1) Remove all those `On Error Resume Next`, they are hiding your errors even though they still occur. You cannot fix your code if you don't see the error(s). Remove them then run the code again to see if any error pops out. 2) *the email will missing my pasted range* - Which *pasted range* are you referring to? the *picture/chart* or the *table* or both? – Raymond Wu Nov 30 '21 at 06:28
  • @RaymondWu Thank you for (1) info. will remove. 2) Both. chart and table are missing in the email. – Shining Star Nov 30 '21 at 06:36
  • 1
    Tried a quick mock-up and I can produce the email fine with the picture and table. You might need to give excel some time to paste the table, try adding `DoEvents` after `rng1.Copy`. Also read on [how to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Selecting Cell/Sheet is a bad practice and should be avoided. – Raymond Wu Nov 30 '21 at 06:54
  • @RaymondWu, now after I remove `On Error Resume Next `, the error is on the pasted code `wordDoc.Range(Start:=wordDoc.Range.End - 1).PasteAndFormat wdChartPicture` is this because it not in HTML? – Shining Star Dec 03 '21 at 01:42
  • What error code and description did you get? I could paste mine fine so I'm not exactly sure what could cause it. – Raymond Wu Dec 03 '21 at 02:34

0 Answers0