MICROSOFT KE MASTER BANO BY A.K.V: January 2021

Sunday, January 24, 2021

automatic delete data from excel sheet by vba

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next

Dim Metric As String

Metric = Target.Value

Select Case Metric

Case "my company"

Range("a3:k15").Value = " "

Case "1/1/2022"

Range("a3:d1000").Value = " "

Case "1/2/2022"

Range("a3:d1000").Value = " "

Case "1/3/2022"

Range("a3:d1000").Value = " "

Case "1/5/2022"

Range("a3:d1000").Value = " "

Case "1/7/2022"

Range("a3:d1000").Value = " "

Case "1/9/2022"

Range("a3:d1000").Value = " "

Case "1/11/2022"

Range("a3:d1000").Value = " "

Case "1/12/2022"

Range("a3:d1000").Value = " "

Case "1/13/2022"

Range("a3:d1000").Value = " "

Case "1/14/2022"

Range("a3:d1000").Value = " "

Case "1/15/2022"

Range("a3:d1000").Value = " "

Case "1/16/2022"

Range("a3:d1000").Value = " "

Case "2/16/2022"

Range("a3:d1000").Value = " "

Case "2/16/2022"

Range("a3:d1000").Value = " "

End Select

End Sub

active cell highlight in excel by vba coading

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next

Dim Metric As String

Metric = Target.Value

Select Case Metric

Case "Revenue"

Worksheets("Data").Range("F2").Value = Metric

Case "Gross Profit"

Worksheets("Data").Range("F2").Value = Metric

Case "Net Profit"

Worksheets("Data").Range("F2").Value = Metric

Case "Employees"

Worksheets("Data").Range("F2").Value = Metric

End Select

End Sub

Thursday, January 21, 2021

log in box in excel by vba

Private Sub CommandButton1_Click()

If TextBox1.Text = "apple" Or TextBox2.Text = "banana" Then

Application.Visible = True

Unload Me

Else

TextBox1 = ""

TextBox2 = ""

MsgBox "wrong details"

End If

End Sub

and show form

Private Sub Workbook_Open()

Application.Visible = False

UserForm1.Show

End Sub

Sunday, January 17, 2021

Automatic save file any location by vba

Sub SaveInvoiceWithNewName()

    Dim NewFN As Variant
    ' Copy Invoice to a New Workbook
    ActiveSheet.Copy
    NewFN = "FILEPATH\" & Range("i5").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    NextInvoice
End Sub

Monday, January 11, 2021

DATE FULL FUNCTION IN EXCEL

Dosto ye meri excel ki videos he ise dekhe aur smjhe

YE KUCH FORMULE HE

WEEKDAY

MONTH

EOMONTH

WORKDAY

TODAY

EDATE

=DATEDIF(J21,NOW(),"Y")

=DATEDIF(J21,NOW(),"YM")

=DATEDIF(J21,NOW(),"MD")

Saturday, January 9, 2021

Jump any sheet by coading

'Program Created by Learn More Channel

Sub Enter_Sheet_Number()

Dim sheetnum As Integer
On Error GoTo ErrorMessage
sheetnum = InputBox("Enter Sheet No")
If sheetnum > Sheets.Count Then
MsgBox "Sheet Not Aviable"
Else
Sheets(sheetnum).Activate
End If
ErrorMessage:
If Err.Number = 13 Then
Err.Clear
MsgBox "Invlaid Sheet No"
End If

End Sub

Sub Enter_Sheet_Name()

Dim sheetname As String
sheetname = InputBox("Enter Sheet Name")
On Error GoTo ErrorMessage
Sheets(sheetname).Activate
ErrorMessage:
If Err.Number = 9 Then
Err.Clear
MsgBox "Invlaid Sheet Name"
End If

End Sub

Saturday, January 2, 2021

Automatic send mail ceoit by google sheet

This software is specially developed by TNS for use by its clients

Unauthorized use & copying of this software will attract penalties.

For support contact ea@tnspl.in

function onOpen() {

var mySheet = SpreadsheetApp.getActiveSpreadsheet();

var menuEntries = [{name: "Send Email Now", functionName: "sendEmail"},

{name: "Send Test Email", functionName: "testEmail"},

{name: "Create Trigger", functionName: "setupTrigger"},

{name: "Delete Trigger", functionName: "deleteTrigger"},

{name: "Bounce Report", functionName: "bounce"},

{name: "Show Email Quota", functionName: "showQuota"},

{name: "Help / About", functionName: "showHelp"}];

mySheet.addMenu("CEOITBOX", menuEntries);

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

mySheet.getRange("K1").setValue("Remaining Quota: "+MailApp.getRemainingDailyQuota());

var authInfo = ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL);

Logger.log(authInfo.getAuthorizationStatus());

}

function getQuota() {

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

mySheet.getRange("K1").setValue("Remaining Quota: "+ MailApp.getRemainingDailyQuota());

}

function showQuota() {

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

mySheet.getRange("K1").setValue("Remaining Quota: "+ MailApp.getRemainingDailyQuota());

Browser.msgBox("Remaining Quota: "+ MailApp.getRemainingDailyQuota());

}

function bounce(){

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn());

var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

var ss="from:(mailer-daemon@google.com OR mailer-daemon@googlemail.com)";

var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

var count = 0;

var threads = GmailApp.search(ss,0,500);

for (var t in threads)

{

var message = threads[t].getMessages()[0];

var i=message.getPlainBody();

var l=i.match(/Delivery to[\s\S]+?(\S+\@\S+)\s([\s\S]+?)-----/)||i.match(/Your message wasn't delivered to (\S+\@\S+) ([\s\S]+)/);

var a='=HYPERLINK("URL";"View")'

if(l)

{

var n=l[2].match(/The response.+:\s+(.+)/)||l[2].match(/The error.+:\s+(.+)/)||l[2].match(/Technical details.+:\s+(.+)/)||["",l[2].trim()];

count++;

var msgDt=threads[t].getLastMessageDate();

var reason = n[1].replace(/ (Please|Learn|See).*$/,"");

var msgSub = threads[t].getFirstMessageSubject();

e.appendRow([msgDt,l[1],reason,msgSub]);

threads[t].moveToTrash();

}

if ( count != 0)

{

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn());

e.appendRow([toDay, 'Logged these bounced emails & deleted them: ',count]);

}

function unsubscribe(){

var ss = SpreadsheetApp.getActiveSpreadsheet();

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("B Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn());

var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

var t = 'subject:unsubscribe';

var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

var count = 0;

var threads = GmailApp.search(t,0,500);

Logger.log(ss);

for (var t in threads) {

var message = threads[t].getMessages()[0];

var i=message.getFrom();

var l=i.split('<')[1];

var k = l.split('>')[0];

e.appendRow([threads[t].getLastMessageDate(),k,"Unsubscribe Request from: "+i,message.getSubject()]);

threads[t].moveToTrash();

// GmailApp.sendEmail(i, "your unsubscribe request", "We have received your unsubscribe request");

count++;

}

if ( count != 0)

{

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn());

e.appendRow([toDay, 'Unsubscribed from this Newsletter: ',count]);

}

function delbounced()

{

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

var data = sheet.getDataRange().getValues();

var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

var count = 0;

for(var i = 0; i<data.length;i++){

if(data[i][3] == "Bounced"){

count++;

var message="Removed "+data[i][1]+" - "+data[i][3];

sheet.getRange(i+1,5).setValue(message).setFontColor('#ff0000');

sheet.getRange(i+1,1).setValue("");

sheet.getRange(i+1,2).setValue("");

sheet.getRange(i+1,3).setValue("");

// sheet.deleteRow(i+1);

}

if ( count != 0)

{

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn());

e.appendRow([toDay, 'Removed from Contacts due to Bounced or Unsubscribed', count]);

}

function getTrackingGIF(account, email, subject) {

var imgURL = "https://ssl.google-analytics.com/collect?"

+ "v=1&t=event"

+ "&tid=" + account

+ "&z=" + Math.round((new Date()).getTime()/1000).toString()

+ "&cid=" + Utilities.getUuid()

+ "&ec=" + encodeURIComponent("Email Open")

+ "&ea=" + encodeURIComponent(subject)

+ "&el=" + encodeURIComponent(email);

return "<img src='" + imgURL + "' width='1' height='1'/>";

}

function sendEmail() {

var emailSender = Session.getEffectiveUser().getEmail();

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

var myContacts = mySheet.getDataRange();

var myContact = myContacts.getValues();

var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

var newLine = " ";

var emailSalutation = template.getRange("B9").getValue();

var emailUnsub = emailSender ;

var emailReplyTo = template.getRange("B5").getValue();

var emailYourName = template.getRange("B6").getValue();

var emailccto = template.getRange("B7").getValue();

var emailbccto = template.getRange("B8").getValue();

var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

var umsg1= "Note: This email was sent to ";

var umsg2= '. If you do not wish to receive these emails in future <a href="mailto:'+emailUnsub+'?subject=UNSUBSCRIBE&body=Please%20Unsubscribe%20me">Click Here</a>.';

var count=0;

var subArray= template.getRange(11, 2, 48).getValues();

var bodArray= template.getRange(11, 3, 48).getValues();

var emailReport = template.getRange("D3").getValue();

var analyticsID = template.getRange("D2").getValue();; // Google Analytics ID

var emailPattern = /^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;

var msgURLold='';

for (i=1; i < myContact.length; i++) {

var person = myContact[i];

if (emailPattern.test(person[1]) != false && person[1] != "" && person[0] != "" && person[5] >= 1)

{

var emailSub = subArray[person[5]-1];

var msgURL = bodArray[person[5]-1];

if (emailSub != "" && msgURL !="")

{

var emailMsg1= emailSalutation + " " + person[0] + ","+newLine ;

var emailMsg2= newLine+ umsg1 + person[1]+ umsg2;

if (msgURLold != msgURL){ var msgBody= SitesApp.getPageByUrl(msgURL).getHtmlContent()};

var emailMsg =emailMsg1+ msgBody+emailMsg2;

emailMsg += getTrackingGIF(analyticsID, person[1], emailSub); //Tracking Code

var advancedArgs = {htmlBody:emailMsg, name:emailYourName, replyTo:emailReplyTo, cc:emailccto, bcc:emailbccto};

GmailApp.sendEmail(person[1], emailSub, emailMsg , advancedArgs);

mySheet.getRange(i+1,5).setValue("Sent Msg#"+person[5]+ " on " +toDay);

var count = count + 1;

msgURLold=msgURL;

}

else

{

if(emailPattern.test(person[1]) != true & person[1] != "")

{

mySheet.getRange(i+1,5).setValue("Invalid Email Address").setFontColor('#ff0000');

mySheet.getRange(i+1,2).setFontColor('#ff0000');

}

/* catch(e) {

var f=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

f.getRange(2,1,f.getLastRow(),f.getLastColumn());

f.appendRow([toDay, e.message+' File:'+e.fileName+' Line:'+e.lineNumber,0]); }*/

if ( count != 0)

{

mySheet.getRange("I1").setValue("Actual Emails Sent:"+count);

var emailSheduled = mySheet.getRange("H1").getValue();

var ss = SpreadsheetApp.getActiveSpreadsheet();

var emailMsg = emailYourName + ", " +newLine + "Have sent " + count +" messages today." + newLine +"Scheduled to " + emailSheduled + newLine +" <a href=\"" + ss.getUrl() + "\">Open spreadsheet</a> "+ " " +"Powered by www.CEOITBOX.com " ;

var emailSubject = count + " Email Newsletters sent today";

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn());

e.appendRow([toDay, emailSubject+' from this email id: '+emailSender,count]);

GmailApp.sendEmail(emailReport, emailSubject , emailMsg , {htmlBody:emailMsg});

var firstUse = mySheet.getRange("Z1").getValue();

if ( firstUse != "1")

{

var emailMsg = emailSalutation + " " + person[0] + "," + newLine + emailMsg+ newLine+ umsg1 + person[1]+ umsg2 ;

var advancedArgs = {htmlBody:emailMsg, name:emailYourName, replyTo:emailReplyTo, cc:emailccto, bcc:emailbccto};

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn()).clearContent();

e.appendRow([toDay, emailSubject+' from this email id: '+emailSender,count]);

GmailApp.sendEmail("info@ceoitbox.com", "Script used - CEOITBOX NL Merge" , emailMsg , advancedArgs );

mySheet.getRange("Z1").setValue("1").setFontColor('#ffffff');

}

else

{

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn());

e.appendRow([toDay, 'No Emails sent from ' + emailSender,0]);

}

mySheet.getRange("K1").setValue("Remaining Quota: "+MailApp.getRemainingDailyQuota());

SpreadsheetApp.flush();

}

function testEmail() {

// THIS FUNCTION IS USED FOR SENDING TEST EMAILS

var emailSender = Session.getActiveUser().getEmail();

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");

var myContacts = mySheet.getDataRange();

var myContact = myContacts.getValues();

var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

var newLine = " ";

var emailSalutation = template.getRange("B9").getValue();

var emailUnsub = emailSender ;

var emailReplyTo = template.getRange("B5").getValue();

var emailYourName = template.getRange("B6").getValue();

var emailccto = template.getRange("B7").getValue();

var emailbccto = template.getRange("B8").getValue();

var toDay = Utilities.formatDate(new Date(), "GMT+5:30", "dd-MMM HH:mm");

var umsg1= "Note: This email was sent to ";

var umsg2= '. If you do not wish to receive these emails in future <a href="mailto:'+emailUnsub+'?subject=UNSUBSCRIBE&body=Please%20Unsubscribe%20me">Click Here</a>.';

var count=0;

var emailReport = template.getRange("D3").getValue();

var testTemplate = template.getRange("D4").getValue();

var analyticsID = template.getRange("D2").getValue();

var subArray= template.getRange(11, 2, 48).getValues();

var bodArray= template.getRange(11, 3, 48).getValues();

var emailSub = subArray[testTemplate-1];

var msgURL = bodArray[testTemplate-1];

template.getRange("D5").setValue("");

if (testTemplate != "" && emailReport != "" && emailSub !="" && msgURL !="")

{

var emailMsg1= emailSalutation + " " + emailYourName + ","+ newLine ;

var emailMsg2= newLine+ umsg1 + emailReport+ umsg2;

var emailMsg =emailMsg1+SitesApp.getPageByUrl(msgURL).getHtmlContent()+emailMsg2;

var trackGIF=getTrackingGIF(analyticsID, emailReport, emailSub); //Tracking Code

emailMsg += trackGIF;

var advancedArgs = {htmlBody:emailMsg, name:emailYourName, replyTo:emailReplyTo, cc:emailccto, bcc:emailbccto};

GmailApp.sendEmail(emailReport, emailSub, emailMsg , advancedArgs);

template.getRange("D5").setValue("Sent Test Msg for Template # "+testTemplate);

var e=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S Report");

e.getRange(2,1,e.getLastRow(),e.getLastColumn());

e.appendRow([toDay, "Sent Test Msg for Template # "+testTemplate+' from this email id: '+emailSender,1]);

}

else {template.getRange("D5").setValue("Nothing sent !");}

}

function setupTrigger() {

// First - Deletes all triggers in the current project.

var triggers = ScriptApp.getProjectTriggers();

for (var i = 0; i < triggers.length; i++) {

ScriptApp.deleteTrigger(triggers[i]);}

// Second - Setup new trigger in the current project.

var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

var emailSendTime = template.getRange("B4").getValue();

var emailSender=Session.getActiveUser().getEmail();

ScriptApp.newTrigger('sendEmail').timeBased().everyDays(1).atHour(emailSendTime).create();

ScriptApp.newTrigger('bounce').timeBased().everyDays(1).atHour(22).create();

ScriptApp.newTrigger('unsubscribe').timeBased().everyDays(1).atHour(21).create();

ScriptApp.newTrigger('delbounced').timeBased().everyDays(1).atHour(23).create();

template.getRange("D6").setValue('Trigger has been setup for '+emailSendTime+' Hrs from this email id: '+emailSender);

Browser.msgBox('Trigger has been setup to send emails automatically at '+emailSendTime+' Hrs from this email id: '+emailSender);

}

function deleteTrigger() {

// Deletes all triggers in the current project.

var template = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template");

var triggers = ScriptApp.getProjectTriggers();

for (var i = 0; i < triggers.length; i++)

{

ScriptApp.deleteTrigger(triggers[i]);

template.getRange("D6").setValue('No Triggers are active');

}

Browser.msgBox('All Triggers have been deleted!');

}

SpreadsheetApp.flush();