Free Microsoft Excel 2013 Quick Reference

Email lotus notes excel attachement Results

I get Run-time error '7000':

Notes error: Database already contains a document with this ID (UNID)

If I close Notes/Excel, then start Notes and Excel, the 1st run through seems OK but some times on subsequent runs it has this error (not every time).

How is the UNID generated?

I am using the COM method.

Thanks


	VB:
	
) 
    Dim noSession As Object, noDatabase As Object, noDocument As Object 
    Dim obAttachment As Object, EmbedObject As Object 
    Dim stSubject As Variant, stAttachment As String 
    Dim vaRecipient As Variant, vaMsg As Variant 
    Dim noDocumentTemp As Object 
     
     
    Const EMBED_ATTACHMENT As Long = 1454 
     
     'Retrieve the path and filename of the active workbook.
    stAttachment = ActiveWorkbook.FullName 
     
     'Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession") 
    Set noDatabase = noSession.GETDATABASE("", "") 
     
     'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL 
     
     'Create the e-mail and the attachment.
    Set noDocument = noDatabase.CreateDocument 
     
     'Add the attachment to existing richtext object (body), to avoid split paragraph error
    Set obAttachment = noDocument.CreateRichTextItem("Body") 
     
     'Append text into the rich text item
    obAttachment.AppendText (BodyText) 
     
     'Add a new blank or two into the body
    obAttachment.AddNewLine (2) 
     
     ' Add the attachment
    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment) 
     
     
     ' If no email address, send to self and then fwd or print for true recipient
    If Recipient = "" Then 
        toRecipient = UserName 
    Else 
        toRecipient = Recipient 
    End If 
     
     
     ' Put recipients into array (, splits them)
    Dim torecpos As Variant 
    Dim ccrecpos As Variant 
     
     ' Split "to" recipients by comma
    torecpos = Split(toRecipient, ",") 
    toreccount = UBound(torecpos) 
    Dim toRec() As Variant 
    Redim toRec(toreccount) 
    For i = 0 To toreccount 
        toRec(i) = torecpos(i) 
    Next i 
     
     ' Split "cc" recipients by comma
    If ccRecipient  "" Then 
        ccrecpos = Split(ccRecipient, ",") 
        ccreccount = UBound(ccrecpos) 
        Dim ccRec() As Variant 
        Redim ccRec(ccreccount) 
        For i = 0 To ccreccount 
            ccRec(i) = ccrecpos(i) 
        Next i 
    Else 
        ccreccount = -1 
    End If 
     
     
     'Add values to the created e-mail main properties.
    With noDocument 
        .Form = "Memo" 
        .SendTo = toRec 
        If ccreccount >= 0 Then 
            .Copyto = ccRec ' if blank, keep blank
        End If 
        .Subject = Subject 
        .SaveMessageOnSend = True 
    End With 
     
     
     'Send the e-mail.
    With noDocument 
         ' Decide if send immed or save in drafts
        If Method = "Send immediately" Then 
             'Send the document
            .PostedDate = Now() 'Gets the mail to appear in the sent items folder
            .send 0 
        Else 
             ' Save in drafts
            .Save True, True 
        End If 
    End With 
     
     
     'Release objects from the memory.
    Set EmbedObject = Nothing 
    Set obAttachment = Nothing 
    Set noDocument = Nothing 
    Set noDatabase = Nothing 
    Set noSession = Nothing 
     
     'Activate Excel for the user.
    AppActivate "Microsoft Excel" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Buen día a todos...

Estoy tratando de mandar un e-mail automático a varias direcciones de correo electrónico dependiendo según los datos filtrados (que no mande a los que no están en la columna A, por ejemplo).

De mrexcel.com tomé un código que manda la hoja de excel como attachment al e-mail que está en el código VBA, pero no logro que tome los datos ni siquiera de una celda de la hoja, mucho menos de una columna filtrada.

PLEASE HELP!

Intenté esto:
Code:
dim correos as Range
' varias instrucciones
correos.value = Range("A1")
.SendTo = correos
'suponiendo que en A1 está el correo "justcantmakeit@almosttired.com"
pero sólo me sale cuando lo pongo así:
Code:
Aquí está TODO lo demás:
Code:
Sub SendLotusNote()

' be sure to reference the Lotus Domino Objects, domobj.tlb
Dim objNotesSession As Object
Dim objNotesDatabase As Object
Dim objNotesDocument As Object
Dim objAttachment As Object
Dim objRichText As Object
Dim FullPath As String
Dim FileName As String
Dim Msg As String

Const EMBED_ATTACHMENT = 1454

Set objNotesSession = CreateObject("Notes.Notessession")
Set objNotesDatabase = objNotesSession.GetDatabase("", "")
Call objNotesDatabase.OpenMail 'default mail database
If objNotesDatabase.IsOpen = False Then
MsgBox "Cannot connect to Lotus Notes."
Exit Sub
End If
Set objNotesDocument = objNotesDatabase.CreateDocument
Call objNotesDocument.replaceitemvalue("Form", "Memo")

'Do ' prompt user for file name and location
FullPath = Application.GetSaveAsFilename
'Loop Until FullPath  False
' save to new loc; Lotus only sends last-saved copy
ActiveWorkbook.SaveAs FullPath
FileName = ActiveWorkbook.Name

' assemble message
Set objRichText = objNotesDocument.CreateRichTextItem("Body")
Set objAttachment = objRichText.EmbedObject(EMBED_ATTACHMENT, "", _
FullPath, FileName)
Msg = "Lotus Note sent from " & objNotesSession.CommonUserName
With objNotesDocument
.Subject = "Weekly Report"
.Body = Msg
'   Declare a Message Subject
'    Subject = InputBox("Please Enter the Message Subject", "Message Subject")
'        MailDoc.Subject = Subject
'            If Subject = "" Then MailDoc.Subject = "Volunteer Update"
'        frmE_Mail.Show
'    MailDoc.Body = Message
'
.SendTo = "icannotdoti@butitry.com"
.SaveMessageOnSend = True ' save in Sent folder
.Send (False)
End With

Set objNotesSession = Nothing
Set objNotesDatabase = Nothing
Set objNotesDocument = Nothing
Set objAttachment = Nothing
Set objRichText = Nothing
MsgBox "Your Lotus Notes message was successfully sent ..." & _
Chr$(13) & _
Chr$(13) & _
"The Excel form will now auto-close.", vbInformation, "Message Sent """
ActiveWorkbook.Close SaveChanges:=False

End Sub
[/list]


Hi I read the tip from Mr.Excel and have tried it. The workbook gets attached and the subject appears correctly however the email address is missing.

I am using lotus Notes 5 and Excel 97 however when i run the macro the email address does not appear in the send "TO" also is there a way I cc other email address.

Would really appreciate your assistance or is there another method I can use.ActiveX add in is not added. Would appreciate if you could advise which ActiveX ADD-IN I should use.

Thanks

Sub SendIt()
Application.Dialogs(xlDialogSendMail).Show _
arg1:="ask@mrexcel.com", _
arg2:="This goes in the subject line"
End Sub

Hello,

I am trying to write some vba code that will work with either Outlook, Outlook Express or Lotus notes.

I do NOT want to attach a file. All I want is the body text to be generated from the contents of the file as currently calculated. An example could be:

Cell a1: 13-Jan-08
Cell a2: £3,000

Email body text would therefore be:

Sales at 13-Jan-08 = £3,000

I would also like to customised the subject line and set up a list of email addresses that the mail needs to go to.

Can abnybody help me with some code to do this?

Many thanks!

I have been trying to get sendmail from excel to work with lotus notes

I have thought about trying to use a mailto: but cant think how to do it. I can get it to work in Outlook no problem but we use Lotus Notes r5 at work.

My button in excel to outlook allows me to click, create a seperate excel file with that specific worksheet only placed in it. I use

Code:
.Cells.Copy
        .Cells.PasteSpecial Paste:=xlPasteValues
to only get values not formula.

The new file is called by worksheetname.xls

The Outlook message is addressed and headed in the code also the new file is attached to the email.

This is exactly what I wish to do with notes - is it possible?

By the way the user will be logged in to notes and it will be open.

Thanks for your help.

Rob

Hi,

I need to include a message in the body of an email. I have an Excel file
that has a macro button that when clicked will open Lotus Notes email with
the Excel file attached. However I would like to include some standard
language in the body of the email and have gone through some of the links
here and have not had any success when creating my macro so that the message
will appear in the body of the email. Any help would be greatly appreciated.

I have 2 Japanese Windows 2000 and Japanese Office 2000 Standard pc which uses Lotus Notes version 5.0.7. We received a attached excel spreadsheet on one of the machines and it does not open. Excel starts up but it is blank. On the other Japanese PC running the exact same stuff, the spreadsheet comes up fine. I have placed all the add-ins for excel and this still did not work.

Hello,

I receive some log update email everyday, and I have to copy the attachement to one file in excel.

Is it possible to write a macro that would get everyday the email with "status-update.logXXXX" on the subject and open the attachement on "VIEW" select the content and copy it to a spreadsheet named "daily log changes" ?

Regards,
Rafael

Hello,

I am using the below code to automatically email excel reports via Lotus Notes and it works great. My question is that at times I will run this macro and there will be no report to send that day (an error obviously will occur as the macro cant locate the file). Is there an IF, THEN statement I can include in the code so if the file does not exist the macro should simply end? Any help would be appreciated. Thanks!

'Set up the objects required for
Automation into lotus notes
    Dim Maildb As Object 'The mail database
    Dim UserName As String 'The current users notes name
    Dim MailDbName As String 'THe current users notes mail database name
    Dim MailDoc As Object 'The mail document itself
    Dim AttachME As Object 'The attachment richtextfile object
    Dim Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)
    Dim stSignature As String
    
    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")
    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) &
".nsf"
    'Open the mail database in notes
    Set Maildb = Session.GETDATABASE("", MailDbName)
     If Maildb.IsOpen = True Then
          'Already open for mail
     Else
         Maildb.OPENMAIL
     End If
    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    
    'Attach Your Signature
    stSignature = Maildb.GetProfileDocument("CalendarProfile") _
                                            .GetItemValue("Signature")(0)
    
    'MailDoc.Recipient = Recipient
    Addressee = "Jerad.Pearson@usbank.com"
    Recipient = Split(Addressee, ",")
    MailDoc.sendto = Recipient
    
    'MailDoc.Recipient = CopyTo
    'MailDoc.copyto = "Jerad.Pearson@usbank.com"
    
    'MailDoc.Recipient = BlindCopyTo
    'MailDoc.blindcopyto = "Jerad.Pearson@usbank.com"
    
    'MailDoc.Subject = Subject
    MailDoc.Subject = "Retail Processing Report"
    'MailDoc.Body = BodyText
    MailDoc.Body = "Hello,  Attached you will find a copy of your Error Report." & stSignature
    'MailDoc.SAVEMESSAGEONSEND = SaveIt
    MailDoc.SAVEMESSAGEONSEND = True
    
    'Set up the embedded object and attachment and attach it
    Attachment = "G:ShareFinancial and Quality Control and TrainingReportingTransfer AgencyData InformationProcessing
Team 1.xls"
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment, "Attachment")
       
    End If
    'Send the document
   MailDoc.SEND 0, Recipient
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
End Sub


I upgraded to Excel 2010 from 07 last week. When I went to use this Macro, it no longer worked. This worked perfect in 07 and I have even since pulled up several different files with this macro that did work in 07. Nothing has changed at all besides the upgrade. The line with the error is red below.

This macro is to generate a string of emails in Lotus Notes without sending them. There is some verbiage that is automatically included in the email (some in the macro and some in a cell with a formula...

Your help is greatly appreaciated.

Sub EmailNotes()
Dim objNotesSession As Object
Dim objNotesDb As Object
Dim objNotesDoc As Object
Dim vaRecipients As String
Dim r As Long
Dim Msg As String
On Error GoTo Error_Handling

Application.ScreenUpdating = False

Set session = CreateObject("Notes.NotesSession")
strServer = session.GetEnvironmentString("MailServer", True)
strMailfile = session.GetEnvironmentString("MailFile", True)

Set db = session.GETDATABASE(strServer, strMailfile)' Set uiws = CreateObject("Notes.NotesUIWorkspace")
'
' If db.IsOpen = True Then
' 'Already open for mail
' Else
' db.OpenMail
' End If

For r = 9 To Range("BA65536").End(xlUp).Row
'Create the e-mail and add the attachment.
' Set objNotesDoc = objNotesDb.CREATEDOCUMENT

Msg = ""
Msg = Msg & Range("BF" & r) & "," & vbCrLf & vbCrLf
Msg = Msg & Range("BD" & r) & "." & vbCrLf & vbCrLf
Msg = Msg & "Thank you," & vbCrLf
Msg = Msg & "John Doe"

vaRecipients = Range("BA" & r)

CreateAndDisplayNotesEmail vaRecipients, Range("BE" & r) & " Timecard ", Msg, ""

'increment for the next person
r = r + 23
Next

MsgBox ("The e-mails have successfully been distributed."), vbInformation
ExitSub:
'Release objects from memory.
Set db = Nothing
Set session = Nothing
Set uiws = Nothing
Exit Sub
Error_Handling:
MsgBox "Error number: " & Err.Number & vbNewLine & _
"Description: " & Err.Description, vbOKOnly
Resume ExitSub
End Sub


Hi there all,

I've recently started a new job and one of my duties is monitoring and reporting Office electrical usage/demand data. I send every office a weekly email with attachments.. normally 3 attachments showing their overall electrical consumption data and drilling down in to different depts etc.

I've found this task to be really repetitive since there are over 20 office locations.. so every week I've to create over 20 emails... add 3 attachments to each and change the office names in the text etc

The only difference between the emails is the office name and the week number. Therefore the only thing that will change every week is the week number. This is the same with the file names and folder names and email subject etc.

If you see my attachment I have set up 3 worksheets, each with an office.
Each office's email recipients will differ.

Everything on each sheet will stay the same every week apart from the week number. So when I update the week number, how would I go about updating the red txt in my attachment... e.g the week number in the subject, and file names and paths etc

How about setting this up so when I hit the Mail button, excel will create an email, fetch the attachments via the file path (can excel do this?) and show the email (MS Outlook or Lotus Notes) ready for me to hit the send button (this will let me review the email and make sure I haven't made any boo boo's lol).

Any pointers would be a great help.

Thanks loads

I have been trying to get a sheet copied from Excel to Lotus Notes and emailed. I found some code that does almost what I want. The only thing that I would like to change is to have it not auto send the email once created. I would like the user to have to click on send in Lotus Notes to complete the task.

Also I would like the subject line to refer to a cell on the sheet.

Any help would be great.

Thanks

Private Sub CommandButton1_Click()

Dim Maildb As Object        'The mail database
    Dim UserName As String      'The current users notes name
    Dim MailDbName As String    'The current users notes mail database name
    Dim MailDoc As Object       'The mail document itself
    Dim AttachME As Object      'The attachment richtextfile object
    Dim Session As Object       'The notes session
    Dim EmbedObj As Object      'The embedded object (Attachment)
    Dim Subject As String       'The subject string
    Dim Attachment As String    'The path to the attachemnt string
    Dim Recipient As String     'The Recipient string (or you could use the list)
    Dim Recip(10) As Variant    'The Recipient list
    Dim BodyText As String      'The body text
    Dim SaveIt As Boolean       'Save to sent mail
    Dim WasOpen As Integer      'Checking to see if the Mail DB was already
                                'open to determine if session should be
                                'closed (0) or left alone (1)
    Dim ClipBoard As DataObject 'Data object for getting text from clipboard
    Subject = "Sheet1!a1"
    Recipient = "kmatlock40@gmail.com"
    Sheets("Sheet1").Select
    Range("A3:G44").Select
    Selection.Copy
    Set ClipBoard = New DataObject
    ClipBoard.GetFromClipboard
    SaveIt = True
    Set Session = CreateObject("Notes.NotesSession")
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) &
".nsf"
    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
         WasOpen = 1      'Already open for mail
     Else
         WasOpen = 0
         Maildb.OPENMAIL    'This will prompt you for password
     End If
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient      'Or use Racip(10) for multiple
    MailDoc.Subject = Subject
    MailDoc.body = ClipBoard.GetText(1)
    MailDoc.SAVEMESSAGEONSEND = SaveIt
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
        MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.SEND 0, Recipient
    'Clean Up'
    Range("A1").Select
    Application.CutCopyMode = False
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set EmbedObj = Nothing
    If WasOpen = 1 Then
        Set Session = Nothing
    ElseIf WasOpen = 0 Then
        Session.Close
        Set Session = Nothing
    End If
    
    MsgBox "The Line Down Email was sent", vbOKOnly
        
End Sub


Hi,
I am using Lotus Notes as my email application. Whenever I open an excel attachment, a blank excel file ,named "Book1" ,will also be created and shown on the monitor together with the attachment. Hence I have two opened excel file. Kindly advice me how to prevent this from happening. I am using Microsoft Office 2003.

Thanks
Regards

I am using the following code to attach my active workbook as a lotus notes email attachment. Is it possible to edit the code so it will attach it as a zip file.

Thank you for your time and help.

Sub SendWithLotus()
   Dim noSession As Object, noDatabase As Object, noDocument As Object
   Dim obAttachment As Object, EmbedObject As Object
   Dim stSubject As Variant, stAttachment As String
   Dim vaRecipients As Variant, vaMsg As Variant
   Dim vacopypeople As Variant
   Dim lnLastRow As Long
   Dim lnLastRow2 As Long
   
   Dim saveit As Variant
   saveit = Range("saveit")
 
   Const EMBED_ATTACHMENT As Long = 1454
   Const stTitle As String = "Status Active workbook"
   Const stMsg As String = "The active workbook must first be saved " & vbCrLf _
         & "before it can be sent as an attachment."
   
   'Check if the active workbook is saved or not
   'If the active workbook has not been saved at all.
   If Len(ActiveWorkbook.Path) = 0 Then
      MsgBox stMsg, vbInformation, stTitle
      Exit Sub
   End If

   'If the changes in the active workbook has been saved or not.
   If ActiveWorkbook.Saved = False Then
      If MsgBox("Do you want to save the changes before sending?", _
            vbYesNo + vbInformation, stTitle) = vbYes Then _
            ActiveWorkbook.Save
   End If
   'Create the list of recipients.
      lnLastRow = Cells(Rows.Count, "Q").End(xlUp).Row
      vaRecipients = Range("Q2:Q" & lnLastRow).Value
      lnLastRow2 = Cells(Rows.Count, "R").End(xlUp).Row
      vacopypeople = Range("R2:R" & lnLastRow2).Value
   'the message
  vaMsg = ""
        vaMsg = vaMsg & Range("Body1") & vbCrLf & vbCrLf
        vaMsg = vaMsg & Range("Body2") & vbCrLf & vbCrLf
        vaMsg = vaMsg & Range("Body4") & vbCrLf & vbCrLf
        vaMsg = vaMsg & Range("Body3") & vbCrLf & vbCrLf
        vaMsg = vaMsg & Range("Sig1") & vbCrLf
        vaMsg = vaMsg & Range("Sig2") & vbCrLf
        vaMsg = vaMsg & Range("Sig3") & vbCrLf
        vaMsg = vaMsg & Range("Sig4") & vbCrLf

   'Add the subject to the outgoing e-mail which also can be retrieved from the users
   'in a similar way as above.
   stSubject = Range("Subject")

   'Retrieve the path and filename of the active workbook.
   stAttachment = ActiveWorkbook.FullName

   'Instantiate the Lotus Notes COM's Objects.
   Set noSession = CreateObject("Notes.NotesSession")
   Set noDatabase = noSession.GETDATABASE("", "")

   'If Lotus Notes is not open then open the mail-part of it.
   If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

   'Create the e-mail and the attachment.
   Set noDocument = noDatabase.CreateDocument
   Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
   Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)

   'Add values to the created e-mail main properties.
   With noDocument
      .Form = "Memo"
      .SendTo = vaRecipients
      .CopyTo = vacopypeople
      .Subject = stSubject
      .Body = vaMsg
      .SaveMessageOnSend = saveit
   End With
   'Send the e-mail.
   With noDocument
      .PostedDate = Now()
      .Send 0, vaRecipients
   End With
   'Release objects from the memory.
   Set EmbedObject = Nothing
   Set obAttachment = Nothing
   Set noDocument = Nothing
   Set noDatabase = Nothing
   Set noSession = Nothing

   'Activate Excel for the user.
   AppActivate "Microsoft Excel"
   MsgBox "The e-mail has successfully been created and distributed.", vbInformation
End Sub


Hi!
I have an Excel Workbook and would like create a command button that would automatically create an email (via Lotus Notes) to a defined recipient, with a defined subject line, defined email content, with my workbook attached to the email too. Is this possible in Excel 2007? Help!
Colleen

I am sending out emails (Lotus 8.5.2) with Excel spreadsheet attachments (Excel 2003) using vba code (thank you Ron De Bruin). The process has worked great, but now I want to add another spreadsheet column that will contain a formula that I want to be included in the attachment the email recipient receives (i.e., the recipient opens the attachment and enters their vehicle mileage into the spreadsheet and the conditional formula would display an error based on their input). With my current code, the formula does not come across with the attachment, only the actual error message that was in the spreadsheet when it was attached. Confusing, I know. Let me try again. Here is the formula I want to be included in the attachment:

If for example D2 is empty (column C has previous mileage, column D has current mileage), the formula would cause
"ERROR, mileage not reported" to be displayed in E2. The problem is...only the message is what shows up in the
email recipient's attachment (column E) and although it is still an accurate message for the recipient to see, I want the
formula to also be there because the recipient will be asked to enter their mileage into the spreadsheet (and return to us)
and the formula is supposed to alert them with the messages if the mileage they enter is wrong in some way.

Here is the section of my code that does the sending. Was wondering if my problem had to do with the CreateRichTextItem of > Set obAttachment = noDocument.CreateRichTextItem("stAttachment"). I'm a novice at vba code..so any help/direction would be very much appreciated.

Public Sub SendEmail(ByVal pEmail As String, ByVal pEFN As String, ByVal
pNotifType As String, pNotification As String, ByVal pPOC As String)
'Inherits System.Windows.Forms.RichTextBox
  Dim noSession As Object, noDatabase As Object, noDocument As Object
  Dim obAttachment As Object, EmbedObject As Object
  Dim stSubject As Variant, stAttachment As String
  Dim vaRecipient As Variant, vaMsg As Variant
  Dim Password As String
  Dim CurDate
  CurDate = Date
  Dim LDate As String
 
  LDate = (MonthName(DatePart("m", CurDate))) & ", " & DatePart("yyyy", CurDate)
 
  Const EMBED_ATTACHMENT As Long = 1454
  Const stTitle As String = "Active workbook status"x
  Const stMsg As String = "The active workbook must first be  saved " & vbCrLf _
  & "before it can be sent as an attachment."
  Do
    vaRecipient = pEmail
   Loop While vaRecipient = ""
   'If the user has canceled the operation.
   If vaRecipient = False Then Exit Sub
 
     If pNotification = "Final mileage due to Govt" Then
       vaMsg = "Removed for the message board " & LDate & vbCrLf & vbCrLf
 
     ElseIf pNotification = "Gas cutoff notification" Then
       vaMsg = "Removed for the message board" & vbCrLf & vbCrLf
 
     ElseIf pNotification = "Third notice" And pNotifType <> "PM service due notification" Then
       vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _
     Else
       'First and second notices (third notice is above)
     Do
       vaMsg = "Removed for the message board, " & vbCrLf & vbCrLf _
 
     Loop While vaMsg = ""
     End If
 
     'If the user has canceled the operation.
     If vaMsg = False Then Exit Sub
     Do
       stSubject = pEFN
     Loop While stSubject = ""
     stAttachment = ActiveWorkbook.FullName
 
     'Instantiate the Lotus Notes COM's  Objects.
     Set noSession = CreateObject("Notes.NotesSession")
     Set noDatabase = noSession.GETDATABASE("", "")
 
     'If Lotus Notes is not open then open the mail-part of it.
     If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
     'Create the e-mail and the attachment.
     Set noDocument = noDatabase.CreateDocument
     Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
 
     Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
 
     'Add values to the created e-mail main properties.
     With noDocument
      .Form = "Memo"
      .SendTo = vaRecipient
      .Subject = stSubject
      .Body = vaMsg
      .SaveMessageOnSend = True
     End With
     'Send the e-mail.
     With noDocument
      .PostedDate = Now()
      On Error GoTo Err_Handle
      .Send 0, vaRecipient
     'Release objects from the memory.
      Set EmbedObject = Nothing
      Set obAttachment = Nothing
      Set noDocument = Nothing
      Set noDatabase = Nothing
      Set noSession = Nothing
 
      Exit Sub
Err_Handle:
      If vaRecipient = " " Then
        MsgBox pPOC & " has no email address in column L of your Master spreadsheet"
      Else
        MsgBox " Either the Group is setup wrong in this program for " & pPOC & " or something else is
going on.  Alert Catherine"
      End If
     End With
 
   'Release objects from the memory.
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
 
End Sub



First, hello all, i this my first post in this forum, im in desperate need of HELP...!

I been fiddling with the idea to notifying some of my users of the expiration date of their accounts, that i have in a excel spreadsheet.

Every thing works good, ecxept for on small detail. The main function that validates the date, takes reference from the current date + 7 so it gives a week's notice to the end users, and it does send the email, but, it only send it to the first user that meets the condition requirements. The rest of the listed users are not validated, hence the are not notified, even if they meet the date conditions.

I have used as referenece the following:
http://www.excelforum.com/excel-prog...piry-date.html
http://www.mrexcel.com/forum/showthread.php?t=139949
http://www.bygsoftware.com/Excel/VBA...otus_notes.htm

I just whant to mention that this is the firt time i use VB for macros and it has taken me 3 days to get where i am right now. Please see attached file so you can better understand the sitiuation.

In advaced thank you for your help.

Best Regards.

Hi ,

I have a script which will open another excel sheet , updates the data and send it through an email as an attachment . Everything works fine . Now I want the data to be sent as a picture and not just as an attachment .

I want excel to copy the cells I define and paste it in the new mail . Can you help me with this request . I have attached the spreadsheet which has the script in it

Hi,

I have very little knowledge of VBA but have managed to scrape my way through to getting the end product I need. Basically Within a sheet I have email addresses and within cells B9, B11, B13 there is a file path to a file that will attach when the vba is executed.

The problem is that sometimes a file attachment will not be required and when the cell values in either/all cells B9, B11, B13 are blank the vba script will not run because the error states that there is no file to attach.

Is there a way for teh script to check if there is a file location, if not then skip it to the next file and so on until it sends just a blank email if required.

Hope that makes sense. Code is below. Main issues are around the
    Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
Full Code is here:

Sub SendEmailGroups()
 
    Dim noSession As Object, noDatabase As Object, noDocument As Object
    Dim obAttachment As Object, EmbedObject As Object
    Dim stSubject As Variant, stAttachment As String
    Dim vaRecipient As Variant, vaMsg As Variant
     
    Const EMBED_ATTACHMENT As Long = 1454
    Const stTitle As String = "Active workbook status"
    Const stMsg As String = "The active workbook must first be  saved " & vbCrLf _
    & "before it can be sent as an attachment."
     'Check if the active workbook is saved or not
     'If the active workbook has not been saved at all.
    If Len(ActiveWorkbook.Path) = 0 Then
         MsgBox stMsg, vbInformation, stTitle
        Exit Sub
    End If
     'If the  changes in the active workbook have been saved or not.
    If ActiveWorkbook.Saved = False Then
        If MsgBox("Do you want to save the changes before sending?", _
        vbYesNo + vbInformation, stTitle) = vbYes Then _
        ActiveWorkbook.Save
    End If
    bccRecipient = Range("A3:A100").Value
    
    'vaRecipient = Range("D2").Value
     
    vaMsg = Range("G2").Value
     
    stSubject = Range("F2").Value
    
    stAttachment = Range("B9").Value
        stAttachment2 = Range("B11").Value
            stAttachment3 = Range("B13").Value
    ' stAttachment = ActiveWorkbook.FullName
     'Instantiate the Lotus Notes COM's  Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")
     'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
     'Create the e-mail and the attachment.
    Set noDocument = noDatabase.CreateDocument
    Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
    
        Set obAttachment = noDocument.CreateRichTextItem("stAttachment2")
    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment2)
    
            Set obAttachment = noDocument.CreateRichTextItem("stAttachment3")
    Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment3)
    
     'Add values to the created e-mail main properties.
    With noDocument
        .Form = "Memo"
       ' .SendTo = vaRecipient
        .BlindCopyTo = bccRecipient
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
    End With
     'Send the e-mail.
    With noDocument
        .PostedDate = Now()
        .Send 0, vaRecipient
    End With
     
     'Release objects from the memory.
    Set EmbedObject = Nothing
    Set obAttachment = Nothing
    Set noDocument = Nothing
    Set noDatabase = Nothing
    Set noSession = Nothing
     
     'Activate Excel for the user.
    AppActivate "Microsoft Excel"
    MsgBox "The e-mail has successfully been created and distributed.", vbInformation
End Sub
Hope someone can help!

Thanks

I have a excel based form, upon completion, there is a button attached
"Submit", which will send email & attached that form to email. With Outlook
it is working fine. I need help for Lotus notes. Now, I have few user, who
uses
Lotus notes as email application. The version used by them is 6.5.2. I wrote
below macro, but not working, can someone help to fix this as well. What I
need is form with a custome button "submit" & upon cliking it should check
whether all required field is filled in & send send mail to "To" & "CC" with
that form as an attachment. Thanks in advance.

Sub lotus()
'
' lotus Macro
' Macro recorded 7/6/2005 by Abdulkader
'

' Dim objNotesSession As Object
Dim objNotesMailFile As Object
Dim objNotesDocument As Object
Dim objNotesField As Object

'Function SendMail()

On Error GoTo SendMailError

EmailSendTo = "ak.bhanpurawala@hp.com"
EmailCCTo = "bhanpura@emirates.net.ae"

'Establish Connection to Notes
Set objNotesSession = CreateObject("Notes.NotesSession")

'Establish Connection to Mail File
' .GETDATABASE("SERVER","FILE")
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
'Open Mail
objNotesMailFile.OPENMAIL

'Create New Memo
Set objNotesDocument = objNotesMailFile.CREATEDOCUMENT

'Create 'Subject' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject",
EmailSubject)

'Create 'Send To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", EmailSendTo)

'Create 'Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EmailCCTo)

'Create 'Body' of memo
Set objNotesField = objNotesDocument.CREARERICHTEXTITEM("Body")

With objNotesField
.APPENDTEXT "Please find attached herewith the form duly filled in.
Looking forward toward receiving your quote. Thanks"
End With

'Attach the file --1454 indicate a file attachment
objNotesField = objNotesField.EMBEDOBJECT(1454, "", EmailSubject)
'objNotesField = objNotesField.EMBEDOBJECT(1454,"",ActiveWorkbook.FullName)

'Send the e-mail
objNotesDocument.Send (0)

'Release storage
Set objNotesSession = Nothing
Set objNotesMailFile = Nothing
Set objNotesDocument = Nothing
Set objNotesField = Nothing

'Set return code
SendMail = True

'Exit Function

SendMailError:
Dim Msg
Msg = "Error#" & Str(Err.Number) & "was generated by" & Err.Source & Chr(13)
& Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext

SendMail = False

End Sub

--
Message posted via http://www.officekb.com


No luck finding an answer? You could always try Google.