none
Excel Sending Email via VBA - Suppressing the Warnings RRS feed

  • Question

  • Hello everybody,

    I would be very appreciative if someone more knowledgeable than would help me out. I am building a VBA code in Excel that will send an Excel File as an attachment in an email to somebody using Outlook. I can get everything to work just fine except that a warning message pops up and it is quite irritation and negates the usefulness of the macro if I have to constantly click on [Allow].

    Any help with how to suppress the warning messages would be greatly appreciated! 

    Note: My company's IT will not let me download add-ins or programs not on their "safe" list.

    Also, I have included the important parts of my code below.

    Thanks!

    Eric

    --------------------------------------------------------------------------------------

        

    Sub Email_Finance()

        Dim oApp As Object
        Dim oMail As Object
        Dim WB As Workbook
        Dim FileName As String
        Dim wSht As Worksheet
        Dim shtName As String
        Dim wsTab As Worksheet

        Application.ScreenUpdating = False
        Application.DisplayAlerts = False

        ' The section below goes to Outlook to create an email, fill in some details, and then emails it out.

        Set oApp = CreateObject("Outlook.Application")
        Set oMail = oApp.CreateItem(0)
        With oMail

            ' Uncomment the line below to hard code a recipient

            .To = "Eric@domain.com"
            .Cc = "Eric@domain.com"

            ' Uncomment the line below to hard code a subject

            .Subject = "G&A Monthly Report - Finance"

            ' Uncomment the lines below to hard code a body

            .body = "Dear Eric," & vbCrLf & vbCrLf & _
              "Please find attached the G&A report for this month. Please review and adjust accordingly. " & vbCrLf & vbCrLf & _
              "Thanks, Eric"
            .Attachments.Add WB.FullName
            .Display
            .Send
        End With

    Wednesday, January 28, 2015 11:56 PM

Answers

All replies

  • See http://www.outlookcode.com/article.aspx?id=52 for the list of your options.

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Thursday, January 29, 2015 2:32 AM
  • Good morning,

    I'm struggling with the step before our esteemed OP.

    I have written some VB to send a sheet as a new workbook attached to an email.

    I can create the new workbook, and send the email perfectly, but I have no idea why the document isn't attached when I try it?

    Sub EmailDuetoExpire()
    ' Macro to run an email report when a line item reaches 28 days before expiry.
    
    Dim i As Integer
    Dim My_filenumber As Integer
    Dim LogStr As String, PlannerName As String, ProjName As String
    Dim MastCount As Integer, subcount As Integer, totsubcount As Integer, Printcount As Integer
    Dim Count As Integer, Percentcount As String, Time As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim MasterName As String
    
    My_filenumber = FreeFile
    
    Count = 0
    i = 1
    Time = Now()
    
        
    For i = 1 To 10000
        
        Cells(i, 2).Select
        
        If Cells(i, 5) = "Yes" Then
        ActiveCell.EntireRow.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        ActiveCell.Insert
        Sheets("Sheet1").Select
        Cells(i, 2).Select
        End If
        
        If ActiveCell = "" Then
        GoTo Last
        End If
        
        Count = Count + 1
    Next i
     
    Last:
    
    Sheets("Sheet2").Select
        Sheets("Sheet2").Copy
        TempFilePath = Environ$("temp") & "\"
        TempFileName = "Due to Expire Log" & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    'Working in Excel 2000-2016 and borrowed online from rondebruin.nl
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
        With OutMail
            .to = "me@email.com"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Test"
            .Body = "Test with attachment"
            .Attachments.Add ActiveWorkbook.FullName
            .Send
        End With
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub
    
    
    
    

    Help would be enormously appreciated.

    Once that's done I'll too need to tackle the warnings issue, but for that I'll follow the link somebody has already provided for help first.

    Best wishes,

    Phil

    Thursday, December 1, 2016 11:03 AM
  • This question has nothing to do with the original question. Please start a new thread in the future.

    In your particular case, Workbook.FullName returns the name of the workbook (e.g. "Book1"), not the file name. Are you saving it as file (e.g. Workbook.SaveAs)?


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Monday, December 5, 2016 3:46 PM
  • Thanks for your response. Apologies, I clearly was wrong when I thought the topics were well enough connected.

    I was under the impression that this bit of the code would create a temporary file which I could send by email:

    Sheets("Sheet2").Select Sheets("Sheet2").Copy TempFilePath = Environ$("temp") & "\" TempFileName = "Due to Expire Log" & " " & Format(Now, "dd-mmm-yy h-mm-ss")

    You're quite right that I get "Book2" from Workbook.FullName, so that element of my script clearly isn't helping me either.

    Phil

    Monday, December 5, 2016 4:12 PM
  • You are not using TempFilePath or TempFileName anywhere in your code...

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Monday, December 5, 2016 4:27 PM
  • Doh.

    So if I add in a workbook.SaveAs TempFilePath & TempFileName after what I copied in to my previous post, I might have a little more luck?

    Cheers,

    Monday, December 5, 2016 4:32 PM
  • yes.

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Monday, December 5, 2016 4:41 PM