Send email from Microsoft excel with customized sheet data

Hi folks,

Have you ever faced requirement where you have to send email from an excel with excel data. If you are in that situation, this blog makes your life easier.

Pre-requisites

Send customized excel data of each row as an email. You should have data in each row and you should know have recipient e-mail address

You should have configured e-mail client like outlook, mail of desktop

Procedure

1. Use Hyperlink function of excel
• Syntax
• =HYPERLINK("mailto:"&e-mailCellNo&"?subject="&subjectCellNo&"&body="&bodyCellNo&"%0A","Send email")
• Help
• Cellno in syntax should point to your data cell number
• %0A - New line
• mailto - The recipient mail address
• subject - the text
• body - the body text
• Note
• If you have any special characters, then use ENCODEURL function
• Don't put the ENCODEURL function in HYPERLINK function
• Make sure, ENCODEURL output points to a different cell and use that cell number in HYPERLINK
2. Use $ to use the same cell data repeatedly
3. After doing the hyperlink to a cell, you should be able to view "Send email"
4. Click on the send email
5. Opens the configured/default e-mail client by filling the recipient, subject and body content from excel sheet
6. Click on "send" or "Ctrl+Enter" keyboard shortcut to send email

Hope, this helps you to explore excel and send email with excel data to many recipients with single click

Please send your valuable feedback and comments to psrdotcom@gmail.com

Microsoft Excel Copy row values to another sheet next available row when the value changes

Hi friends,

My friend had given me a problem in excel of copy row whenever the values changed to another sheet next available row.

I have come-up with this following code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Target.ActiveSheet.Range("A1:D1")) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        MsgBox ("Hi")
        Call ValueChange
        Application.EnableEvents = True
    End If
End Sub
Sub ValueChange()
        Dim lastrow As Long
        
        lastrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1
        Sheets("Sheet2").Range("A" & lastrow & ": D" & lastrow).Value = Sheets("Sheet1").Range("A1:D1").Value

End Sub

Hope it will help you.

Note:
Please share your valuable comments and feedback to psrdotcom@gmail.com

AP SSC (X / 10th) class board duplicate marksmemo / certificate and challan form

Hi friends,

One of my friends lost an SSC certificate and applied for a duplicate.

Required Documents

1. Police FIR against lost certificate
2. Fill the Duplicate SSC Form (Link to Form)
3. Fill this Challan (Link to Challan) with 250 rupees towards the fee Payable at SBI Treasury Branch only

Note: This challan is available on the SSC website under MNR formats

Hope you will get your duplicate certificate quickly with this process.

Take Nokia Phone Contacts (Phonebook) backup in Excel Spreadsheet or .csv file by using OVI PC Connectivity

Hi friends,

Today I have come up with phonebook backup in Nokia. I previously used very old nokia model which doesn't contain the Nokia PC/Ovi Suite connectivity. So, I used to take backup of my contacts in excel sheet by doing data entry. But now most of phones does have the facility to connect to Nokia Ovi/PC Suite to take the backup and maintain their data in Computer(PC).

Here, I have taken backup of my contacts in PC and converted that to Excel sheet to quick access of my contacts.

Pre-Requisites:

1. Make sure that, your phone does support Mobile to PC connectivity via Nokia Ovi/PC Suite
2. Download and install Nokia Ovi/PC Suite
3. Make sure that, you have the PC connectivity cable to the corresponding mobile
4. Make sure that, all your contacts are on device(mobile) memory instead of SIM

• Note: Copy all the SIM contacts to device(mobile)

Procedure:

• Connect your mobile to PC
• Select "Nokia Ovi PC Suite" connection from your mobile
• After successful connection, you will get the similar screen like below

• Select "Contacts" from the image icons on top of the window
• Now, all your contacts will be displayed on the 2nd pane(column) in the Window as shown below

• Select all the contacts (Note: First select any one of contact and do the any one of the below)
• Option 1: Use Keyboard shortcut Control + A
• Option 2: Right click on the contact and choose "Select All" option
• Option 3: Select "Edit" menu and choose "Select All" option
• Now, you can "Drag and Drop" the selected contacts to PC contacts  
• Hint: Drag and drop the content to "Contacts" (Red Circle)

By now, you have successfully taken the backup of your contacts to PC

View/Save the contacts in Spreadsheet/.csv file

• Download the file GrabMyContacts from this link 
• Install it in any folder
• Run the "grabmycontacts.exe", you will get the following screen

• Default "Step 1 - Search for your contacts file" tab will be selected
• Click the "Search for it" button, it will automatically searches your Nokia Ovi contacts database file as shown below

• Click on "Yes"
• Now, choose the "Step 2 - Choose and display your data" tab
• Click on "Push this button to display your contacts"
• Now, it will display all your contacts in the sheet
• Finally, Choose "Step 3 - Export to Excel" tab
• Click on "Finished ! Now Export to Excel !" button
• It will show, your file named "myovicontacts.xml" has been saved on desktop

• Click on "Yes"

• Then, it will ask you to open the file Excel, as shown in the below screen

• Click on "Yes"

• If the default program for the xml is not Excel then it will open with the default program. 
• Open the XML file in Excel:
• To see your contacts in excel, open the new Excel workbook and "Drag and Drop" the "myovicontacts.xml" in it
• Remove the unnecessary columns and save your contacts as "Excel Workbook"/".csv" file format