Send via Outlook using VBA

[nmgmarques]

Hi guys.

Trying to get this script to work using snippets found scouring the web. The macro saves the file using values in D6 and G6 as filename and thenattempts to send an email to users notifying them of the new file. However the code breaks at

Dim olns As Outlook.Namespace

It throws a "Compile error: User-defined type not defined" and pressing ok highlights

olns As Outlook.Namespace

This is the full code thus far:

Sub SaveAsExample()
' Gravar ficheiro com nome igual ao número de ECM e indicar se é Não Aplicável
    Dim FName           As String
    Dim FPath           As String
    Dim NApl            As String
    
    
     
    FPath = "L:\10_CBR_Common\Assuntos gerais\Gestão ECM"
    FName = Sheets("ECM").Range("D6").Text
    NApl = Sheets("ECM").Range("G6").Text
    ThisWorkbook.SaveAs Filename:=FPath & "\" & FName & "_" & NApl
    
'Enviar email de notificação

Dim strReportName As String
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.Namespace
Dim strTO As String
Dim strCC As String
Dim strMessageBody As String
Dim strSubject As String

Set oLook = CreateObject("Outlook.Application")
'Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)

'*********************** USER DEFINED SECTION ************************
strTO = "mymail@gmail.com;mymail2@gmail.com;mymail3@gmail.com"
strMessageBody = "Criada nova Ficha de Acompanhamento e Implementação de ECM"
strSubject = "Ficheiro Acompanhamento ECM " & FName & "_" & NApl
'*********************************************************************

With oMail
.To = strTO
 .CC = strCC
 .Body = strMessageBody
 .Subject = strSubject

 .Attachments.Add FPath & "\" & FName & "_" & NApl
 .Send
End With

Set oMail = Nothing
Set oLook = Nothing
'Set olns = Nothing


'DB.Close
'tbloutput.Close
'dbLocal.Close
objWorkbook.Close

'Set objmail = Nothing
'Set DB = Nothing
Set tbloutput = Nothing


Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
Set tbloutput = Nothing
Set dbLocal = Nothing

     
End Sub

Any help appreciated!

 

[nmgmarques]

Just figured out I was missing the Outlook Library in Tools - References. Also changed the code to something easier in my opinion. It's working so I'll leave the code if anyone needs it for future reference:

Sub SaveAsExample()
' Gravar ficheiro com nome igual ao número de ECM e indicar se é Não Aplicável
    Dim FName           As String
    Dim FPath           As String
    Dim NApl            As String
    
    
     
    FPath = "L:\10_CBR_Common\Assuntos gerais\Gestão ECM"
    FName = Sheets("ECM").Range("D6").Text
    NApl = Sheets("ECM").Range("G6").Text
    ThisWorkbook.SaveAs Filename:=FPath & "\" & FName & "_" & NApl
    
'Enviar email de notificação

    Dim wb As Workbook
    Dim I As Long

    Set wb = ActiveWorkbook

    On Error Resume Next
    For I = 1 To 3
        wb.SendMail "nmgmarques@gmail.com", _
                    "Novo ECM nº " & FName & "_" & NApl
        If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
End Sub

There is one thing I can't solve however. In my script, I am saving the file as FName & _ & NApl so I end up with a file named 5000023013_NA for example. But if I don't place anything in G6, it still saves with the underscore at the end. is there any way to only save the underscore and G6 if G6 exists? Same thing for the subject. I think I won't be able to solve that using my web browsing savvy :/

 

[daverunt]

Hi,

Check to see if G6 has any text. If it does save the filename with underscore & G6 if not omit them.
You can use the sFileName for the subject line

   'Replace
    ThisWorkbook.SaveAs Filename:=FPath & "\" & FName & "_" & NApl
    
'With
    If NApl <> "" Then
    sFileName = FName & "_" & NApl
   
    Else
    sFileName = FName   
    End If
    
    ThisWorkbook.SaveAs Filename:=FPath & "\" & sFileName
    
    
   ' also
    strSubject = "Ficheiro Acompanhamento ECM " & sFileName

 

[nmgmarques]

Hi,

Check to see if G6 has any text. If it does save the filename with underscore & G6 if not omit them.
You can use the sFileName for the subject line

   'Replace
    ThisWorkbook.SaveAs Filename:=FPath & "\" & FName & "_" & NApl
    
'With
    If NApl <> "" Then
    sFileName = FName & "_" & NApl
   
    Else
    sFileName = FName   
    End If
    
    ThisWorkbook.SaveAs Filename:=FPath & "\" & sFileName
    
    
   ' also
    strSubject = "Ficheiro Acompanhamento ECM " & sFileName

That did the trick! Great stuff. Thanks!

 

[daverunt]

Great. Thanks for getting back.