VBA Archives - Quereact

Asked: August 15, 2022Computer programming

How I sort worksheets by alphabet using vba Excel?

Best Answer

Anonymous

Added an answer on August 15, 2022 at 8:17 pm

hello, try this code it will help you: Private Sub CommandButton1_Click() Application.ScreenUpdating = False 'disable screen update for performance Dim i, j As Integer Dim SheetsCount As Integer SheetsCount = Sheets.Count For i = 1 To SheetsCount - 1 For j = i + 1 To SheetsCount If Sheets(j).NameRead more

hello,

try this code it will help you:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False 'disable screen update for performance
    Dim i, j As Integer
    Dim SheetsCount As Integer
    SheetsCount = Sheets.Count
    For i = 1 To SheetsCount - 1
        For j = i + 1 To SheetsCount
            If Sheets(j).Name < Sheets(i).Name Then
                Sheets(j).Move before:=Sheets(i)
            End If
        Next j
    Next i
    Application.ScreenUpdating = True 'enable screen update
End Sub

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False 'disable screen update for performance

Dim i, j As Integer

Dim SheetsCount As Integer

SheetsCount = Sheets.Count

For i = 1 To SheetsCount - 1

For j = i + 1 To SheetsCount

If Sheets(j).Name < Sheets(i).Name Then

Sheets(j).Move before:=Sheets(i)

End If

Next j

Next i

Application.ScreenUpdating = True 'enable screen update

End Sub

See less

Share
Share

Asked: July 31, 2022Computer programming

How to Send Email in Excel Using VBA?

Anonymous

Added an answer on July 31, 2022 at 3:22 pm

This answer was edited.

Hello, You need to give access Outlook with VBA, you need to activate the Microsoft Outlook Object Library. To activate it, go to Tools in the VBA editor > References. Activate Microsoft Outlook 16.0 Object Library : Then in a module use this subroutine: Sub sendAnEmail(ByRefRead more

Hello,

You need to give access Outlook with VBA, you need to activate the Microsoft Outlook Object Library.

To activate it, go to Tools in the VBA editor > References.

Activate Microsoft Outlook 16.0 Object Library :

Then in a module use this subroutine:

Sub sendAnEmail(ByRef DomainName As String, ByRef SendTo As String, ByRef EmailSubject As String, ByRef EmailBody As String)

    'refer to outlook application
    Dim EmailApp As Outlook.Application
    Dim Source As String
    'launch outlook application
    Set EmailApp = New Outlook.Application
    'refer to a new outlook email
    Dim EmailItem As Outlook.MailItem
    'create a new outlook email
    Set EmailItem = EmailApp.CreateItem(olMailItem)
    
    EmailItem.To = SendTo 'the email adress who the email is sent to
    EmailItem.Subject = EmailSubject 'write your email subject in this variable
    EmailItem.HTMLBody = EmailBody
    Source = ThisWorkbook.FullName
    'send the new email
    EmailItem.Send
End Sub

Sub sendAnEmail(ByRef DomainName As String, ByRef SendTo As String, ByRef EmailSubject As String, ByRef EmailBody As String)

'refer to outlook application

Dim EmailApp As Outlook.Application

Dim Source As String

'launch outlook application

Set EmailApp = New Outlook.Application

'refer to a new outlook email

Dim EmailItem As Outlook.MailItem

'create a new outlook email

Set EmailItem = EmailApp.CreateItem(olMailItem)

EmailItem.To = SendTo 'the email adress who the email is sent to

EmailItem.Subject = EmailSubject 'write your email subject in this variable

EmailItem.HTMLBody = EmailBody

Source = ThisWorkbook.FullName

'send the new email

EmailItem.Send

End Sub

To test the code, call this code using a buton:

Sub testSendEmail()
    Dim SendTo, EmailSubject, EmailBody As String
    SendTo = "myemail@example.com"   ' put the email adress that will receive the email
    EmailSubject = "This is a send email test from Excel"  ' write your subject
    EmailBody = "This email is sent from Execl using Microsoft outlook library" ' put the body
    sendAnEmail DomainName, SendTo, EmailSubject, EmailBody  ' call the subroutine by passing the parameters
End Sub

Sub testSendEmail()

Dim SendTo, EmailSubject, EmailBody As String

SendTo = "myemail@example.com" ' put the email adress that will receive the email

EmailSubject = "This is a send email test from Excel" ' write your subject

EmailBody = "This email is sent from Execl using Microsoft outlook library" ' put the body

sendAnEmail DomainName, SendTo, EmailSubject, EmailBody ' call the subroutine by passing the parameters

End Sub

Test: