Email with Attachment using Excel VBA

One of my friend ask me, is it possible to send multiple emails with attachments using Excel VBA. I said YES, everything is possible. Below is just small code that can help you to do it.

Note: Before you apply following code you need to add reference of OutLook Private Sub SendEmailWithAttachment() Dim outlookApp As Outlook.Application Dim eMail As Outlook.MailItem Dim sourceFile, toEmail as String Set outlookApp = New Outlook.Application Set eMail = outlookApp.CreateItem(olMailItem) For i = 2 To 10 ' this is a loop where you will fatch the data from excel rows sourceFile = Cells(i, 2) ' getting path of the file you added in your sheet eMail.Attachments.Add sourceFile toEmail = Cells(i, 1) & ";" eMail.To = toEmail eMail.Subject = "Add your Fix Subject Line or you can get data from the excel" eMail.Body = "Hi," & vbNewLine & "This is your body area" & vbNewLine & "Thanks" eMail.Send Next i End Sub

Happy Coding :)

Datatables - Keeping selected page number after callback

I save the DataTable state in the local storage to avoid passing the page number all over my app. This is how I do it:

$('#offersTable').dataTable({ "bStateSave": true, "fnStateSave": function (oSettings, oData) { localStorage.setItem('offersDataTables', JSON.stringify(oData)); }, "fnStateLoad": function (oSettings) { return JSON.parse(localStorage.getItem('offersDataTables')); } });

This is very useful when you go to another page and you want to go back (using the back button) to the last selected page.

reference

Sort JSON Array with Value

Example:Sort JSON Array

mydata = [{ "id": 1, "first_name": "Jean", "last_name": "Esome", "age": 63}, { "id": 2, "first_name": "Rubi", "last_name": "Simonassi", "age": 18}, { "id": 3, "first_name": "Theo", "last_name": "Mully", "age": 34}, { "id": 4, "first_name": "Barbabra", "last_name": "Castagnasso", "age": 82}, { "id": 5, "first_name": "Mahmud", "last_name": "Boothebie", "age": 25}]; function sortResults(obj,prop, asc) { obj.sort(function(a, b) { if (asc) { return (a[prop] > b[prop]) ? 1 : ((a[prop] < b[prop]) ? -1 : 0); } else { return (b[prop] > a[prop]) ? 1 : ((b[prop] < a[prop]) ? -1 : 0); } }); } //Call Function sortResults(mydata,'first_name', false); // true = ascending / false = descending

Selecting distinct values from a JSON

 

eData is the JSON array

var items = eData.map((d)=> {return d.year})

var uniq = items.filter(GetUnique)

function GetUnique(value, index, self) { 
    return self.indexOf(value) === index;
}

Replacing SharePoint Designer with VS Code

“Replacing SharePoint Designer with VS Code”

- install VS Code

- Add extension SPGO to your VS Code

- Execute following 2 thing

------ Connect to workspace

Press F1 : write > SPGO Configuration Workspace

put the URL and some relevant default inputs

-- new SPGO.json file will be created something like:

{

  "sourceDirectory": "src",

  "sharePointSiteUrl": "https://domain.sharepoint.com/itBudget",

  "workspaceRoot": "f:\\SPApps\\itBudget",

  "publishWorkspaceOptions": {

    "destinationFolder": "/",

    "globPattern": "f:\\SPApps\\itBudget\\src\\**\\*.*",

    "localRoot": "f:\\SPApps\\itBudget\\src"

  },

  "publishingScope": "SaveOnly",

  "authenticationType": "Digest",

  "remoteFolders": ["/SiteAssets/Custom/js", "/Pages", "/SitePages"]

}

In case if SiteAssets folder is not getting downloaded, try to add the pattern "/SiteAssets/**/*"

Press F1 again: write > SPGO Populate local workspace

enter : registered EMail and Password.

Source found at following URLs

1) Replacing SharePoint Designer with VS Code Part 1: Setting up the Environment

2) Replacing SharePoint Designer with VS Code Part 2: Configuring SPGo in VS Code

3) https://marketplace.visualstudio.com/items?itemName=SiteGo.spgo

Unable to open SharePoint Designer Site

Unable to open SharePoint Designer Site

Remove all the content from following folders to avoid issue

• %APPDATA%\Roaming\Microsoft\Web Server Extensions\Cache
• %APPDATA%\Microsoft\Web Server Extensions\Cache
• %APPDATA%\Microsoft\SharePoint Designer\ProxyAssemblyCache
• %USERPROFILE%\AppData\Local\Microsoft\WebsiteCache
• %APPDATA%\Microsoft\SharePoint Designer

Create QR-Code with VBA

Function to add QR-Code as image in your file.

Function Insert_QR(codetext As String)
	Dim URL As String, MyCell As Range

	Set MyCell = Application.Caller
	URL = "https://chart.googleapis.com/chart?chs=125x125&cht=qr&chl=" & codetext
	On Error Resume Next
	  ActiveSheet.Pictures("My_QR_" & MyCell.Address(False, False)).Delete 'delete if there is prevoius one
	On Error GoTo 0
	ActiveSheet.Pictures.Insert(URL).Select
	With Selection.ShapeRange(1)
	 .PictureFormat.CropLeft = 10
	 .PictureFormat.CropRight = 10
	 .PictureFormat.CropTop = 10
	 .PictureFormat.CropBottom = 10
	 .Name = "My_QR_" & MyCell.Address(False, False)
	 .Left = MyCell.Left + 25
	 .Top = MyCell.Top + 5
	End With
	Insert_QR = "" ' or some text to be displayed behind code
End Function