VBA Macro: Embed a table in the Lotus notes mail body and send mail

This code is a combination codes picked from different websites

Option Explicit

Sub Send_Row_Or_Rows_1()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
    Dim OutApp As Object
    Dim Maildb As Object
    Dim Session As Object
    Dim MailDbName As String
    Dim UserName As String
    Dim OutMail As Object
    Dim rng As Range
    Dim Ash As Worksheet
    Dim Cws As Worksheet
    Dim Rcount As Long
    Dim Rnum As Long
    Dim FilterRange As Range
    Dim FieldNum As Integer
    Dim mailAddress As String
    Dim ccmailAddress As String
    Dim StrBody As String
    Dim StrBody1 As String
    Dim NRTItemBody As Object
    Dim body As Object
   
   
    Dim stream As Object
   

    On Error GoTo cleanup
    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
          'Already open for mail
     Else
         Maildb.OPENMAIL
     End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
        Set Ash = ActiveSheet
StrBody = "Dear Sir " & "
" & _
              "Text" & "
" & _
              "Text" & "
"
    StrBody1 = "
" & _
    "Text" & "
" & _
       "Text" & "
"
   
    'Set filter range and filter column (Column with names)
    Set FilterRange = Ash.Range("A1:P" & Ash.Rows.Count)
    FieldNum = 1    'Filter column = A because the filter range start in A
    'Add a worksheet for the unique list and copy the unique list in A1
    Set Cws = Worksheets.Add
    FilterRange.Columns(FieldNum).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=Cws.Range("A1"), _
            CriteriaRange:="", Unique:=True
    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))
    'If there are unique values start the loop
    If Rcount >= 2 Then
        For Rnum = 2 To Rcount
            'Filter the FilterRange on the FieldNum column
            FilterRange.AutoFilter Field:=FieldNum, _
                                   Criteria1:=Cws.Cells(Rnum, 1).Value
            'Look for the mail address in the MailInfo worksheet
            mailAddress = ""
            ccmailAddress = ""
            On Error Resume Next
            mailAddress = Application.WorksheetFunction. _
                          VLookup(Cws.Cells(Rnum, 1).Value, _
                                Worksheets("Mailinfo").Range("A1:B" & _
                                Worksheets("Mailinfo").Rows.Count), 2, False)
            ccmailAddress = Application.WorksheetFunction. _
                          VLookup(Cws.Cells(Rnum, 1).Value, _
                                Worksheets("Mailinfo").Range("A1:C" & _
                                Worksheets("Mailinfo").Rows.Count), 3, False)
                               
            On Error GoTo 0
            If mailAddress <> "" Then
                With Ash.AutoFilter.Range
                    On Error Resume Next
                    Set rng = .SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                End With
               Set OutMail = Maildb.CREATEDOCUMENT
                On Error Resume Next
               
                OutMail.Form = "Memo"
                OutMail.sendto = mailAddress
                OutMail.CopyTo = ccmailAddress
                OutMail.Subject = "Action Items List"
               
                Set stream = Session.CreateStream
                Set body = OutMail.CreateMIMEEntity
                Call stream.WriteText(StrBody & RangetoHTML(rng) & StrBody1)
                Call body.SetContentFromText(stream, "text/html;charset=iso-8859-1", ENC_IDENTITY_7BIT)
               
                              
                OutMail.SAVEMESSAGEONSEND = True
               
                OutMail.PostedDate = Now() 'Gets the mail to appear in the sent items folder
                OutMail.SEND 0, mailAddress

                              On Error GoTo 0
                Set OutMail = Nothing
            End If
            'Close AutoFilter
            Ash.AutoFilterMode = False
        Next Rnum
    End If
cleanup:
    Set Session = Nothing
    Application.DisplayAlerts = False
    Cws.Delete
    Application.DisplayAlerts = True
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub
Function RangetoHTML(rng As Range)
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
    'Close TempWB
    TempWB.Close savechanges:=False
    'Delete the htm file we used in this function
    Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

 

VBA Macro: Send active excel sheet using Lotus Notes

Send attachments through lotus notes

http://www.rondebruin.nl/

Things to change are mentioned in red italic

**********************************************

Option Explicit

 

Const EMBED_ATTACHMENT As Long = 1454

 

Const stPath As String = "c:\Attachments"

 

Const stSubject As String = "Weekly report"

 

Const vaMsg As Variant = "The weekly report as per agreement." & vbCrLf & _

                                          "Kind regards," & vbCrLf & _

                                           "Dennis"

 

Const vaCopyTo As Variant = "name@mail.com"

 

Sub Send_Active_Sheet()

 

  Dim stFileName As String

  Dim vaRecipients As Variant

 

  Dim noSession As Object

  Dim noDatabase As Object

  Dim noDocument As Object

  Dim noEmbedObject As Object

  Dim noAttachment As Object

  Dim stAttachment As String

 

  'Copy the active sheet to a new temporarily workbook.

  With ActiveSheet

    .Copy

    stFileName = .Range("A1").Value

  End With

 

  stAttachment = stPath & "\" & stFileName & ".xlsm"

 

  'Save and close the temporarily workbook.

  With ActiveWorkbook

    .SaveAs stAttachment

    .Close

  End With

 

  'Create the list of recipients.

  vaRecipients = VBA.Array("name1@mail.com", "name1@mail.com")

 

  '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 noAttachment = noDocument.CreateRichTextItem("stAttachment")

  Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)

 

  'Add values to the created e-mail main properties.

  With noDocument

    .Form = "Memo"

    .SendTo = vaRecipients

    .CopyTo = vaCopyTo

    .Subject = stSubject

    .Body = vaMsg

    .SaveMessageOnSend = True

    .PostedDate = Now()

    .Send 0, vaRecipients

  End With

 

  'Delete the temporarily workbook.

  Kill stAttachment

 

  'Release objects from memory.

  Set noEmbedObject = Nothing

  Set noAttachment = Nothing

  Set noDocument = Nothing

  Set noDatabase = Nothing

  Set noSession = Nothing

 

  MsgBox "The e-mail has successfully been created and distributed", vbInformation

 

End Sub

 

Self Deception - hindrance to Progress

This post is about how we manage our perception towards others. The earlier post on 'Secret to project success' is about managing stakeholder perceptions. 

But managing our perception is the most fundamental. I realised this aspect when I read a book gifted by my friend - Radhika, with whom I share a good friendship, which actually grew in a very short duration. 

The book is 'Leadership and Self-Deception' from 'The Arbinger Istitute'. 

It talks about 'Self Betrayal', the key ingredient, which impacts our perception. The explanation on self betrayal is rather interesting. 

Self betrayal, is 

'when we don't do something which we feel we should do for others'

The consequence is that we justify ourselves for why we didn't do it and we start blaming the other person for who he has been, that made us not to do. When we start with blaming others and justifying ourselves, we unknowingly make the other person also to be like us. At the end, both of us don't help each other. 

This is a nice concept, and I have placed my life situations - personal and professional, in this behaviour and found it TRUE. 

How do we overcome this? The book emphasises mostly on how we get into this situation (Get into the box- as per the book). How we come out of this, is not discussed in detail in the book. But some explicit hints which come out are

'We should stop resisting others and do what we feel, we should do to them'.

Instead of this, if we want to try other methods like 1) being away from the situation, 2) changing others, 3) changing our behaviour, 4) trying to explain, etc., it will not work.

We need to work in not getting into the 'Self betrayal' mode. And being out of the self betrayal mode is definitely not overwhelming, compared to when we are in it - that's what the book says. 

I tried using this technique, consciously and found there is a difference. But it is not easy, and one need to apply it consciously, until it get into our subconscious mind.

http://devi-projectmanagement.blogspot.in/2012/08/overcoming-self-deception.html

‘What’ and ‘what not’ to delegate?

Are people reporting to you for the first time? First time project manager? First time supervisor? What a great feeling! But along with that feeling comes more responsibility, which is sometimes scary. You need to provide work to your subordinates, which are challenging and at the same time motivating. When your subordinates are fresh out of school, you need to coach them too. Natural mind-set of superiors is to load them to their maximum. This way, they feel that they are being a great supervisor. Sometime, they give them mundane activities, which they would not want to do it themselves. Sometimes they give them tasks that they are supposed to do, meeting with heads of other departments or companies. Sometimes, tasks are also delegated where the superior doesn't want to travel. The problem is even the supervisors are not aware, or not coached on what they should be handling and what should be delegated. Leave alone the first time supervisors, even experienced people heading big departments don’t know the art and purpose of delegation. Sometimes, the superiors don’t trust the sub-ordinates, and hence don’t delegate. In some cases, they feel better to do it themselves, than to delegate, explain and coach the sub-ordinate. Eventually, they end up doing all the work.

Even I faced with a challenge, when people started reporting to me for the first time. First time supervisors need to be trained on ‘delegation’. You need to delegate it right, not too less and not too much, and right job to the right person.

Recently I read an article on ‘PM Network’, by Nicola Hill, PMP, Thomson Reuters, London, England. She had described beautifully, the ‘Mind-set’ and the steps to be taken by supervisors while delegation, and 4 bullet points on the tasks that should never be delegated.

First on the Mind-set: Supervisors should focus on the ‘development needs’ of team members. “The decision on what to delegate ought to center on the development needs of team members” quotes Nicola Hill. She says that supervisors have to identify shining stars for delegating a particular task. But this was not very convincing to me. When you need to develop people by delegation, you need to concentrate on all your sub-ordinates. But have an assessment of the current strengths of your subordinates. The next steps given by Ms. Hill are quite good. You need to give tasks which build upon what he/she is already doing but added with a challenging component. You can also give him tasks in a completely new area. Both these should be incremental, otherwise, it can bring down the confidence of the sub-ordinate. You have to make sure why you are giving him/her the responsibility and make them understand it is a great opportunity to showcase their potential. Closely monitor, coach and help him/her to complete that particular task.

After you understood the expected mind-set while delegating and the steps to delegates, wouldn't it be better to have some clues on what to delegate and what not to delegate.

Ms. Hill says that tasks which can be done 70% (approx.) correctly compared to you, by your sub-ordinate can be delegated.  But apply the next clues on what not to delegate to the above task, to decide on whether it can be delegated. She gives 4 types of tasks which you should not delegate : 1) Tasks for which you are directly responsible, 2) Tasks that you would be unwilling to complete yourself, 3) Tasks that require a level of authorization that a team member does not possess, 4) Tasks for which the necessary tools are not available.

Isn't this a nice thought by Ms. Hill on delegation. This would be definitely useful for both supervisors! as well as for sub-ordinates J.

http://devi-projectmanagement.blogspot.in/2012/12/what-and-what-not-to-delegate.html

A cooking basic - late in my life!

I have seen people cooking vegetables, without water. It cooks so well and it so tasty. My husband is one of them and my mother in law.. My grandmother too.. I have seen them cooking and tried many times. Every time, I used to get some hint and try. But never really worked well. This time, I took another hint 'Fry onions well', until they become soft and translucent. FINALLY WORKED. This was the secret behind those delicious recipes. Instead of 'learning by seeing', I should have done the other way 'learning by questioning'. Probably, I would have learned it many years back.

Dreams

Do dreams come true? May or may not.. It doesn't matter... 

Many times they are weird.. Sometimes in real life, we feel that we have encountered such things before and when we think, we can link it to a previous dream we had. So dreams do come true!

Dreams belong to the individual and its theirs.. No one can take it away from anyone, unlike in real life.

Dreams take you through different emotions, happiness, sadness, love, friendship, fear, anger, etc.

People say it is an amalgamation of our own thoughts.. But what I went through in my dreams were very different.. It is something out of our normal thought process.. Most of the dreams, we forget; Very few we remember..

A lady appeared in my dreams, who I have met a year back, and I had never thought about her.. How and why such things happen? Sometimes we even get answers to our problems in our dreams. I have woken up in the morning happily with answers to my problems. 

Can you force something in your dream? Never. That's what my experience was.. I read somewhere that we can manipulate our dreams. But it has never happened to me.. May be there are techniques! 

But dreams happen out of the blue and they are never connected to what we go through the day, was my experience.. I heard from my daughter she had dreams since she was 3 years old.. She was even able to narrate what she saw in her dream.. Probably she even had dreams before 3, but was unable to communicate.. I was surprised. I don't remember when I started getting dreams and when I was able to narrate them..

Dreams have given me experiences, answers, which I would not have got from people around me in real life.. I think dreams make you stronger and wiser. Dreams are mine, whether they come true or not.. I need not get IPR for it.. They can never be taken away from anyone...

Shopping in Beijing

There are lots of shopping malls in Beijing. Visited 3 of them, i) Yashow market ii) Silk and Pearl Market iii) Tianyi wholesale supermarket. The third one was the best. The only issue in shopping at these markets are they quote very high prices, and you have to bargain to bring it down. The final price you settle, may also not be the best price. I am making a list of some of the things I bought and the prices I paid. I am not sure if I bargained better or got cheated??! This might be useful for people when they go shopping. This should be the starting price for bargaining… General rule is to start at 20% to 30% of what they quote..

A Play Date!

On a very busy day at office, I suddenly get an invite from someone I don’t know for a ‘play date’! Oh, the invite was not for me, but for my daughter from her friend’s mother. First reach of me – “What is a play date?” Where else to go, other than ‘Google’. Here are some few lines from Wikipedia for people who are ignorant like me.

“A play date or playdate is an arranged appointment for children to get together for a few hours to play. Playdates have become the standard for children of many western cultures because the work schedules for busy parents, along with media warnings about leaving children unattended, prevent the kind of play that children of other generations participated in. The intention of a playdate is to give children time to interact freely in a less structured environment than other planned activities might provide. Playdates are different from organized activities or scheduled sports, because they are not usually structured."

I even read through different sites to understand how it is arranged, what do the host do, what do we do, what should we carry? Finally called her friend's mother and fixed the day. Got a small gift for her friend, discussed the "Do's and Don't" with my kid. ALL SET TO GO! I was more excited in the 'play date' than my daughter. Finally we couldn't go, on the fixed date. 

Three weeks later, I called up her friend's mother again, and got the 'play date' fixed. The schedule of the D-day got aligned to accommodate the playdate time, "waking up-breakfast-lunch-sleep-snack". Me and my daughter reached her friend's home at the right time. I was happy to make new friends, with her friend's Mom and her aunt. We began with a light conversation, in getting to know each other. The kids (My daughter, her friend and her little cousin) started to play with the toys. There was 'sharing', 'grabbing' , 'complaining' between them. After a while, we were served some snacks and tea. They reached to point of getting bored in their games. So I called for my free car pick-up (by my husband). By the time, my husband could reach, their games started getting interesting again. My daughter was not willing to part with her friend and toys when her father called from the gate. Finally, managed to come out. 

On the kids's opinion on coming back together for playing,

My kid - " I don't want to come again. She is not sharing her toys"

Her Friend - "I don't want her to come. She is taking my toys"

Me and her friend's mother probably became good friends than these kids!

Got a feeling of completing a big ordeal - "PLAY DATE"

When I travel back in time, me and my brother would jump the compound and go to our friend's house to play. Our friends would also do the same. There was no formal invitation, and never felt embarrassing to go and disturb our friends as well as their families, at any time of the day. I also come from a small town, and this makes me realise that I am many generations behind!

Being Second!

Is being second a better option?

• Second rank in school
• Second position in industry
• Second best to your husband/wife after your kid
• and so many 'second's...

There are so many advantages in being second. You always want to improve, either move to first or lessen the gap to being first. There are the elements of strategy, politics, tactics in the second position, compared to any other positions. 

Contrary to the old school of thought, 'Be Number One' and 'Be the Leader', everybody wants to be the 'SECOND' nowadays. In this position, you enjoy all the benefits, similar to the first position, leaving out the troubles of the first position (constantly under watch, pressure to maintain the first position, ...)