I use this VBA code in a Excel Macro to send a email. Actually i send a range of cell pasted in the mail but i can´t send the file (it´s was created in the same macro) attached.
Can you help me?
I send the code. How can i do for send the Prueba.doc file attached.
Sub Envia_CVP_LOTUS()
oldStatusBar = Application.DisplayStatusBar
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim WordApp As Object
Dim WordDoc As Object
Dim EmbedObj As Object
Application.DisplayStatusBar = True
Application.StatusBar = "Creando el correo..."
Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GetDatabase("", "")
If Not NDatabase.IsOpen Then
NDatabase.OpenMail
End If
'Create a new document
Set NDoc = NDatabase.CreateDocument
With NDoc
.SendTo = "(e-mail address removed)"
.CopyTo = ""
.Subject = "Previsión Marginal de GN para el " & Date + 1
'Email body text, including marker text which will be replaced by the Excel cells
.body = "Envío la previsión de máquinas Marginales de GN y Combustible Alternativo para el día de mañana." & vbNewLine & vbNewLine & "Donde CVP USD = CVP / ( Fn x Cotización U$S)" & vbNewLine & _
"**PASTE EXCEL CELLS HERE**" & vbNewLine & vbNewLine & "Saludos."
.Save True, False
End With
'Edit the just-created document to copy and paste the Excel cells into it
Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)
With NUIdoc
'Find the marker text in the Body item
.GotoField ("Body")
.FINDSTRING "**PASTE EXCEL CELLS HERE**"
'Replace it with the Excel cells
Sheets("CVP").Range("K5:O48").Select
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Add
Selection.Copy
With WordApp.Selection
.TypeParagraph
.PasteSpecial DataType:=9
.WholeStory
.Copy
End With
WordApp.ActiveDocument.SaveAs "C:\prueba.doc"
WordApp.Quit True
Application.StatusBar = "Pegando el listado..."
Application.Wait Now + TimeValue("00:00:02")
.Paste
Application.CutCopyMode = False
.Send
.Close
End With
Set NSession = Nothing
Set WordApp = Nothing
Application.DisplayStatusBar = oldStatusBar
Application.StatusBar = False
End Sub
Can you help me?
I send the code. How can i do for send the Prueba.doc file attached.
Sub Envia_CVP_LOTUS()
oldStatusBar = Application.DisplayStatusBar
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim WordApp As Object
Dim WordDoc As Object
Dim EmbedObj As Object
Application.DisplayStatusBar = True
Application.StatusBar = "Creando el correo..."
Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GetDatabase("", "")
If Not NDatabase.IsOpen Then
NDatabase.OpenMail
End If
'Create a new document
Set NDoc = NDatabase.CreateDocument
With NDoc
.SendTo = "(e-mail address removed)"
.CopyTo = ""
.Subject = "Previsión Marginal de GN para el " & Date + 1
'Email body text, including marker text which will be replaced by the Excel cells
.body = "Envío la previsión de máquinas Marginales de GN y Combustible Alternativo para el día de mañana." & vbNewLine & vbNewLine & "Donde CVP USD = CVP / ( Fn x Cotización U$S)" & vbNewLine & _
"**PASTE EXCEL CELLS HERE**" & vbNewLine & vbNewLine & "Saludos."
.Save True, False
End With
'Edit the just-created document to copy and paste the Excel cells into it
Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)
With NUIdoc
'Find the marker text in the Body item
.GotoField ("Body")
.FINDSTRING "**PASTE EXCEL CELLS HERE**"
'Replace it with the Excel cells
Sheets("CVP").Range("K5:O48").Select
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Add
Selection.Copy
With WordApp.Selection
.TypeParagraph
.PasteSpecial DataType:=9
.WholeStory
.Copy
End With
WordApp.ActiveDocument.SaveAs "C:\prueba.doc"
WordApp.Quit True
Application.StatusBar = "Pegando el listado..."
Application.Wait Now + TimeValue("00:00:02")
.Paste
Application.CutCopyMode = False
.Send
.Close
End With
Set NSession = Nothing
Set WordApp = Nothing
Application.DisplayStatusBar = oldStatusBar
Application.StatusBar = False
End Sub