Free Microsoft Excel 2013 Quick Reference

Using VBA to send a memo from Lotus Notes

I have an excel file with an embedded word document in it. I'm trying to create a macro to send a memo from Lotus Notes that has the embedded document in it. I don't want it as an attachment to the memo. I want it to show up as a "picture" within the memo so others can't edit it. Does anyone know how to do this?


Post your answer or comment

comments powered by Disqus
I know this is strange but, is there a way I can use vba to print a message?

Hello,

I'm trying to use VBA to save a copy of a workbook, I want to just save the
values not all the formulas and links. So I've got the macro to copy the
sheet and then paste special with values only and then I want to Save it. I
want to be prompted where to save it. I've tried using activeworkbook.close
but because I have a workbook before close event macro it just closes without
saving the changes.
Is there anyway I can either stop the before close macro running or can I
get SaveCopyAs to ask for a filename

Hello all,

Can I use VBA to play a WAV file or some other sound file format? I do not want the playing of this sound to open up an external
media player and be played inside of it, I want it to be a sound that just plays (like Windows and other programs events sounds).

--
Thanks for any help anyone can provide,

Conan Kelly

I need to use VBA to build a SumProduct formula and enter it into various cells in my worksheet. This is what I have done so far:

Range("E6").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(('Data This Week'!$B$5:$B$992=B6)*('Data This Week'!$D$5:$D$992=D6)*('Data This
Week'!$L$5:$L$992))-(SUMPRODUCT(('Data Last Week'!$B$5:$B$992=B6)*('Data Last Week'!$D$5:$D$992=D6)*('Data Last
Week'!$O$5:$O$992)))"
When I try to run the procedure, I get Run-time error 1004 "Application-defined or object-defined error". The line starting with "ActiveCell" gets highlighted by the debugger.

The formula works fine when typed directly into the cell. So I have tried to use the macro recorder to record myself entering the formula, but then I get a message saying "Cannot be recorded."

Why is this error occuring and what would I need to do to fix it?

i have an example file see enclosed.

In this file i have two dates. Start and End. I want to use VBA to fill a column from the start date to the end date (with each entry being the first of the month).

Not sure the best way to do this.

Hi All,

I want to use vba to open a file where the name changes slightly each week it comes in. Let's say the file name is f:/Folder A 05212009

I can do this to open it:
But, next week when the file name is Folder A 05282009, it won't open. Is there any way to write the code so that it picks up
this pattern and can open it each week?

I greatly appreciate any help you can give me!

Hello all,
Does anybody know if it is possbile to extract a calender from lotus notes into Excel?

Robert

I am trying to send en email via Lotus Notes using VBA (in Access) but I am unable to get the email to send from a different mail database other than my personal one.

I have tried to explicitly enter the ServerName and DatabaseName in the following section


	VB:
	
 Maildb = session.GETDATABASE("ServerName", MailDbName) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The below excerpt displays the entire code which still sends via my personal Mail Inbox:


	VB:
	
 _ 
    String, bodytext As String, SaveIt As Boolean) As Boolean 
    Dim Maildb        As Object 
    Dim MailDbName    As String 
    Dim MailDoc       As Object 
    Dim AttachME      As Object 
    Dim session       As Object 
    Dim EmbedObj      As Object 
     
    On Error Goto err_SendNotesMail 
     
     ' 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
    MailDbName = "MyDatabaseName.nsf" 
     
     ' Open the mail database in notes
    Set Maildb = session.GETDATABASE("ServerName", 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" 
    MailDoc.sendTo = recipient 
    MailDoc.Subject = Subject 
    MailDoc.Body = bodytext 
    MailDoc.SaveMessageOnSend = SaveIt 
     
     ' Set up the embedded object and attachment and attach it
    If attachment  "" And Dir(attachment)  "" Then 
        Set AttachME = MailDoc.CreateRichTextItem("Attachment") 
        Set EmbedObj = AttachME.EmbedObject(1454, "", attachment, "Attachment") 
         'MailDoc.CREATERICHTEXTITEM ("Attachment")
    End If 
     
     ' Send the document
    MailDoc.Send 0, recipient 
    Maildb.Close 
     ' Clean Up
    Set Maildb = Nothing 
    Set MailDoc = Nothing 
    Set AttachME = Nothing 
    Set session = Nothing 
    Set EmbedObj = Nothing 
    SendMail = True 
     
end_SendNotesMail: 
    Exit Function 
     
err_SendNotesMail: 
    Select Case Err.Number 
Case 429: 
        MsgBox "Error: " & vbCrLf & Err.Description & vbCrLf & "Possible " & _ 
        "cause: Lotus Notes not installed", vbCritical, "Error while initializing LotusNotes" 
Case Else: 
        MsgBox Err.Description & Err.Number, vbCritical, "Error Lotus Notes" 
    End Select 
    Resume end_SendNotesMail 
End Function 

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


I'm a teacher trying to put together an Excel spreadsheet whereby you can click a link next to a student's name and it will create an e-mail to their form tutor. I've managed to almost get there by using "mailto:" - the email is generated with the to, cc and subject fields fine.

My problem is, I can't find out how to send a pre-made file as the body text of the email. I need something to include in the mailto: link like &body="C:/praiseslip.htm" so that a preprepared file is open that can just be filled in by a busy teacher.

At the moment, an example link generated from various cells of the spreadsheet reads

mailto:jb@tacticalvoter.net?&cc=Perryreeves@sch.uk&subject=^8 JBU JOHN BROWN POSITIVE REFERRAL&body=John Brown POSITIVE REFERRAL!

Any advice gratefully received. I daresay there is a much more elegant solution in VBA, but if I can stretch the mailto: hyperlink to do it it will be easier for me.

Thanks.

Hi there

I created a short program in VBA to send emails to addresses in a list I created in Excel. To avoid the annoying message "A program is trying to automatically send e-mail in your behalf", I'm using sendkeys to actually "press" the button in Outlook. However I found out that this sometimes work, sometimes it doesn't, but I have no idea why this does not work 100%. Below the code.

Sub send_email()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Columns("V").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And _
           LCase(Cells(cell.Row, "W").Value) = "yes" Then

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "subject"
                .Body = "Olá " & Cells(cell.Row, "U").Value _
                      & vbNewLine & vbNewLine & _
                        "message " & _
                        Cells(cell.Row, "Y").Value & "."
                .Display
                Application.SendKeys "%s"
                End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub
Can anyone tell me what I did wrong, or any other workaround solution?

Thanks to all

Leo

Hey everyone,

I am wanting to use VBA code to insert the formula =IF(B8="","",VLOOKUP(B8,Information!C4:D200,2,FALSE)) into cell B9 (it is merged to form cell B9:B13).

I have the formula copied into cell M4 on sheet 3 and have tried to reference it but can't seem to do it. I am happy either using VBA to reference it or using a VBA code to insert the text into cell B9

How can i get the formula from sheet 3 into cell B9 in Sheet 1, or simply put the text in.

Thanks in advance.

Shaun

Can anyone point me to any code that may help me do the following. I've searched Google & this forum but not found an example (I did give up after 4 pages of search results!)

A colleague operates a help desk and all mails come into Outlook 2007. She now needs to extract a list of those mails, subject, from, date, time & body into an excel list so that she can produce a report for SLA purposes. In the absence of a helpdesk system, Excel seems to be the best bet we have of getting this info out. The mails are kept in a separate folder.

I'm very comfortable with the Excel side of things, but my VBA is not up to expert standards and I've never used VBA to integrate with other applications.

I'm sure I could make something work if I had the basic building blocks in place, so any pointers would be greatly appreciated.

I need to use to following VBA to send 106 emails, each with an attachment of up to 4mb.

Sub SendEmail()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email_ As String
Dim subject_ As String
Dim body_ As String
Dim attach_ As String

'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")

' Loop through the rows
For Each cell In Columns("a").Cells.SpecialCells(xlCellTypeConstants)

email_ = cell.Value
subject_ = cell.Offset(0, 1).Value
body_ = cell.Offset(0, 2).Value

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email_
.Subject = subject_
.Body = body_
.Display
End With
Next
End Sub In order not to exceed my mailbox limit, I think I need to delete each email after it has been sent. Can anybody advise how I can do this? Or even suggest an alternative solution?

Thanks

Hi there

I don't know if anyone has tried this but it possible using vba to create a new folder in a directory from the text of a textbox?

I have made a userform that I use to collect a persons first name & surname.

I then created a folder in a directory manually (e.g Z:spnzphotos)
I then scan their photo and place it into the folder.
Is it at all possible to have a piece of code that once I press a command button is creates folder ready for me to add the photo to?

I look forward to hearing any possible suggestions.

Thanks in advance!!

I've seen where you can create a macro using VBA to send an email to a receipient(s) in excel 2007. This gets me close but not quite what I need. I have a sales tracking sheet and what I need is the following. When a new prospect is entered into the sheet, I need the macro to read the value in cell O7. IF it is equal to "prospecting", then I need the macro to send the data values for cells A7:I7 as a string to the Assiged Rep listed in cell N7. There are up to 10 Assigned Reps in a dropdown menu in cell N7 and whatever the user selects is WHO I need the email to be sent to. I'd like the email to auto send as soon as the Assigned Rep field is filled in. Can ANYONE help me with this as I know very LITTLE about VBA programming???? Thanks. I've attached a sample workbook (removed client data).

I am sorry to ask such a basic question, but I am trying to use VBA for the first time. I am trying to write code that will look up a cell in a spreadsheet and if the value of the cell is YES, it will generate an email in outlook. The email will have a standard title and content, but it will have blanks that need to be filled with data that is generated within the spreadsheet.

Is there anyone out there who would be able to help me with this.

Many thanks,

Nicola

Hello, I'm French so I apologize if i make any English mistakes!

Here my problem:

I'm looking to send a message through Lotus Notes V6.5 using a Button in a Userform.

I would like to open it in Lotus Notes before sending in order to modify it if needed

I found 2 different codes to solve my problem but I can't reach exactly what I need.

First Code:

Private Sub CommandButton1_Click()


 
'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)

 
If UserFormEMail.ListBox2.ListCount = 0 Then MsgBox "No Hay Proyectos Seleccionados Para Mensaje"
If UserFormEMail.ListBox2.ListCount = 0 Then Exit Sub
 
'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"
MailDoc.Sendto = "CST_BAntoniol@xxxx.com" 'UserFormEMail.TextBox9.Value
MailDoc.CopyTo = ""
MailDoc.Subject = "essaie d'envoi adresse differentes"
' Construction du corps du message
Set objNotesField = MailDoc.CreateRichTextItem("Body")
With objNotesField
.AppendText "Buenos Dias,"
.AddNewline 2
.AppendText "Usted podrìa enviarme el Order Entry Form del (de los) proyecto(s) sigienete(s):"
.AddNewline 2
For i = 0 To UserFormEMail.ListBox2.ListCount - 1
.AppendText UserFormEMail.ListBox2.List(i) & " --- " & UserFormEMail.ListBox3.List(i)
.AddNewline 2
Next i
.AddNewline 2
.AppendText "Un saludo Cordial"
.AddNewline 1
.AppendText "Bruno Antoniol"
.AddNewline 3
End With


MailDoc.SaveMessageOnSend = True
'Set up the embedded object and attachment and attach it

 
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.Send (False)
 
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing


End Sub
With that code, my problem is that the message is sent directly without the possibility of modifying it on Lotus Notes

And second code:

'---------- API -----------
'pour faire passer au premier plan
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long
'pour ouvrir la fenêtre
Private Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, _
                    ByVal nCmdShow As Long) As Long
'pour vérifier si Lotus est ouvert
Private Declare Function FindWindow Lib "user32" Alias _
    "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
     
Dim sSrvr As String 'Le serveur de mail de l'utilisateur courant
Dim MailDbName As String 'Le nom de la base mail de l'utilisateur courant
Dim UserName As String 'Le nom de l'utilisateur courant
 
Dim retVal As Variant 'La valeur de retour de la fonction
 
'---------------- fonction ouverture de session Notes -----------
Function CreateNotesSession() As Boolean
    Const notesclass$ = "NOTES"
    Const SW_SHOWMAXIMIZED = 3 'plein ecran
   Const SW_SHOWMMINIZED = 2 'reduire
   Const SW_SHOWWINDOW = 1 'fenetre
   Const SW_SHOW = 5
     
    Dim Lotus_Session As Object
    Dim rc&
    Dim lotusWindow&
     
'    lotusWindow = FindWindow(notesclass, vbNullString)
 
'    sSrvr = Lotus_Session.GETENVIRONMENTSTRING("MailServer", True)
'    MailDbName = Lotus_Session.GETENVIRONMENTSTRING("MailFile", True)
'    UserName = Lotus_Session.UserName
    
'    DoEvents
   'Ouverture de Lotus Notes
   'Mettre votre chemin d'accès pour notes.exe et notes.ini'
    'retVal = Shell("C:Program Fileslotusnotesnotes.exe =C:Program Fileslotusnotesnotes.ini", vbMaximizedFocus)

    'verifier que Lotus est bien ouvert (recupere le handle)
   lotusWindow = FindWindow(notesclass, vbNullString)
    If lotusWindow <> 0 Then
        rc = ShowWindow(lotusWindow, SW_SHOW)
        rc = SetForegroundWindow(lotusWindow)
        CreateNotesSession = True
    Else
         CreateNotesSession = False
    End If
End Function






Private Sub CommandButton1_Click()

 Const EMBED_ATTACHMENT As Integer = 1454
    Const EMBED_OBJECT As Integer = 1453
    Const EMBED_OBJECTLINK As Integer = 1452
      
    Dim s As Object ' use back end classes to obtain mail database name
   Dim db As Object '
   Dim doc As Object ' front end document
   Dim beDoc As Object ' back end document
   Dim workspace As Object ' use front end classes to display to user
   Dim bodypart As Object '
   Dim bodyAtt As Object '
   Dim lbsession As Boolean
      
    lbsession = CreateNotesSession
      
    If lbsession Then
        'cree la session Lotus Notes
       Set s = CreateObject("Notes.Notessession")
        'se connecte a sa database
       Set db = s.getDatabase(sSrvr, MailDbName)
        If db.IsOpen = True Then
            'database deja ouvert
       Else
            Call db.Openmail
        End If
        'cree un document memo
       Set beDoc = db.CreateDocument
        beDoc.Form = "Memo"
         
         'construction du mail
       Set bodypart = beDoc.CreateRichTextItem("Body")
        'beDoc.From = "Moi" 'inutile
       beDoc.SendTo = UserFormEMail.TextBox9.Value
        beDoc.CopyTo = CCToAdr
        beDoc.BlindCopyTo = BCCToAdr
        beDoc.Subject = UserFormEMail.TextBox10.Value & " Pendiente"
        
       
With bodypart
.AppendText "Buenos Dias,"
.AddNewline 2
.AppendText "Usted podrìa enviarme el Order Entry Form del (de los) proyecto(s) sigienete(s):"
.AddNewline 2
For i = 0 To UserFormEMail.ListBox2.ListCount - 1
.AppendText UserFormEMail.ListBox2.List(i) & " --- " & UserFormEMail.ListBox3.List(i)
.AddNewline 2
Next i
.AddNewline 2
.AppendText "Un saludo Cordial"
.AddNewline 1
.AppendText "Bruno Antoniol"
.AddNewline 3
End With

        '-----------------------------------------
       'Remarque s'il y a des destinataires multiples, il suffit de mettre un tableau
       'd'e-mail dans SendTo (CopyTo,BlindCopyTo)
       'exemple :
       'Dim recip(25) as variant
       'recip(0) = "emailaddress1"
       'recip(1) = "emailaddress2" e.t.c
       'beDoc.sendto = recip
       '----------------------------------------
       ' documents joint 1
       If Len(Attach1) > 0 Then
            If Len(dir(Attach1)) > 0 Then
               Set bodyAtt = bodypart.EmbedObject(EMBED_ATTACHMENT, "", Attach1, dir(Attach1))
            End If
        End If
      
        ' documents joint 2
       If Len(Attach2) > 0 Then
            If Len(dir(Attach2)) > 0 Then
                Call bodyAtt.EmbedObject(EMBED_ATTACHMENT, "", Attach2, dir(Attach2))
            End If
        End If
             
        'Affichage du mail dans Lotus Notes
       Set workspace = CreateObject("Notes.NotesUIWorkspace")
        Call workspace.EditDocument(True, beDoc).FieldSetText("Body", "CORPS DE MESSAGE")
            


      
        Set s = Nothing
        Else
            MsgBox "Open Lotus Notes Before !"
    End If


End Sub
With that code, it opens well the message before sending, but my problem is that I don't know how to write a message with that syntax:

Hello Mr. *ComboBox1(Name)*,

I'm writing you concerning projects:: *ListBox1(Nº of project)*
To ask you the following information: *(if CheckBox1=true then CheckBox1.Caption)*
Please, send it to me before that date: *TextBox2 (date)*

Best reguards,

Bruno
Do you have any solution to modify one of those codes?

Thanks in advance. Don’t hesitate to ask me if you don’t understand me!

Bruno

I'm using Office (2000) and Lotus Notes (6.5).
On my previous PC I used the same software, and I could send an Excel or
Word document by choosing File, Send To, and Mail Recipient (as attachment).
After getting the new PC and installing the software, I don't have the option
to send a file from within Word or Excel. Internet Explorer is set to use
Lotus Notes as my e-mail program, but I don't see a place within Word or
Excel to tell them which e-mail program to use. What am I missing? Thank
you for your help.
-Dan

Hi guys,

I've been desperately looking for an answer on this one and I'm hoping someone can help.
I have created a macro that will copy and paste a range of cells from Excel into a new email in Lotus notes ready to send when I want it to.
My issue at the moment is that all of my emails should contain a signature in the footer: I believe that when I select 'new message' in Lotus notes it opens a new email with my premade signature at the bottom; a sort of template I guess.
My macro at the moment seems to paste over all original text in a new email which of course means that the Signature is removed.
I've tried so many ways to solve this but have yet to have any luck: the closest I have come is pasting another lot of cells that look like the original signature however the Hyperlinks then do not copy over.

If anybody can lend there expertise I would be most grateful!

Thanks in advance

Mr F

p.s. more than happy to paste my code if that makes it easier.

Hi,

I have an excel sheet that is protected. Actually, only certain cells are protected+locked from editing.

I am using vba to update cells on the protected+locked worksheet.

I read that i need this code to do this

                With ActiveSheet
            .Protect userinterfaceonly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True
        End With

The above code is actually in a worksheet enable-change event type thing. Its vba code on the worksheet itself.

However, when i try to use vba to do a cut-n-paste of some values that are in protected cells i get an error saying the worksheet is protected and that i have to "unprotect it".

Any ideas?

Hi,

I have a folder (C:userdatafiles) with a list of files such as:

userdata-01-01-2012.xls
userdata-01-02-2012.xls
userdata-01-03-2012.xls
userdata-01-04-2012.xls

How can I use vba to to open the file with the latest date? I cant use "last modified date" as the files could possibly be opened and changed at later dates. I need to be able to determine the latest date based upon the filename.

Many thanks

Hello All,

I'm trying to use VBA to create a query to return records of one table (PlanTable) which meet the specified criteria of a record in another table (AddTable). These criteria will vary with each record in AddTable, and I'd like to set up the query using variables in the SQL so that this code will return the appropriate records regardless. I'm using the resulting information to populate a two dimensional boolean array so that it makes a grid of sorts that indicates if a given record in PlanTable meets the criteria of a given record in AddTable.

I've never used VBA for an Access query before, so I'm not familiar with the process. Based on some of the other threads, I've got some very basic code for the query.

	VB:
	
 rsAddTable 
    SQLSelect = "SELECT [Plan Table].PlanID " 
    SQLSelect = SQLSelect & "FROM [Plan Table]" 
    SQLSelect = SQLSelect & " WHERE [Plan Table].Carrier = " & ![Carrier] 
    SQLSelect = SQLSelect & " AND [Plan Table].CustomerType  " & ![CustomerType] 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is my SQL not right here? Can this even be done this way? Any help is appreciated.

Hi all

I know you can send e-mails from Lotus Notes using VBA, but can you read the number of emails in certain folders/boxes in Lotus Notes using Excel/VBA?
I need to be able to count the number of e-mails and get the date of each one automatically.
I'm hoping this is possible, or I've just got hold of the biggest piece of work since the painting of the Forth road bridge!!!

Thanks in advance

Richard

Hi Folks,

I have a workbook containing information within a worksheet, i then use VBA to create a new worksheet extracting the information from the original sheet.

I now need a a piece of code to do the following if possible:

For every cell in columns "I" that has the text "Hello" then place 1 as a running total in cell "R3".
For example if the are 26 cells in column "I" that say "Hello" then cell "R3" will equal the total of 26.
I hope this makes sense.

I will be using this code a number of times as there will be at least 8 different texts in column "I".

Thanks in advance for all the help.


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