Expressions for Excel VBA Environ

Here's a list of few expressions for the Environ in Excel VBA

Environ("os")

Environ("tmp")

Environ("path")

Environ("path")

Environ("windir")

Environ("appdata")

Environ("appdata")

Environ("homepath")

Environ("username")

Environ("homedrive")

Environ("userdomain")

Environ("logonserver")

Environ("sessionname")

Environ("systemdrive")

Environ("userprofile")

Environ("computername")

Environ("programfiles")

Environ("allusersprofile")

Environ("processor_level")

Environ("commonprogramfiles")

Environ("processor_revision")

Environ("number_of_processors")

Environ("processor_identifier")

Environ("processor_architecture")

Will keep updating the list as and when i come across new once.

Thanks

Subhajit

Exploring The File System Object_Part 2

In my previous post I explored the few  methods of the fileSystemObject to find all the Sub Folders in a parent folder. Count of all files in them, their size and filetypes.

In this post I have used few more methods to build a macro which will do the following:

1. Does whatever was possible in the last post ;) 
2. Make different folders for different file types
3. Copy all the files from the specified folder
4. Organize the similar file types into their respective folders

This is how the excel file looks,

This is the main() subroutine which calls the organize routine,

This is the routine that organizes different file types into different folders

This is the initial and final output folders:

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.
SUBHAJIT.
.
To Download the example file:
Please click this Google Drive Link

Exploring The File System Object_Part 1

We can use the Microsoft scripting runtime object library for working easily with files and folders.
Start with opening the references and checking the Microsoft scripting runtime library.
This gives a lot of objects collections, methods and properties in the scripting library.
You can browse through them by opening the object library.

Given below is a sample code for exploring few possibilities of the file system object. 

Put any folder link in the range(A2) and see the following information about the folder.

Sub Directories, Total Count of Files, File Types, Count of File Types , Size of Folder

Please find the file link below to see and explore the file.

========================================================================

========================================================================

Will write another blog on exploring properties of file methods.

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.
SUBHAJIT.
.
To Download the example file:
Please click this Google Drive Link

Sending Automated emails - WITH GENERIC AND CUSTOMIZED CONTENT Via Outlook

Hello Everyone,


Long time I have written a blog on Excel VBA.

This blog contains details regarding sending automated emails from an excel template, which is a common thing and you'll find many other blogs for the same.


But What's different in this is:

1. You can use customized names for few user and the generic name for the rest.
2. Send a copy of the mail to an individual person or just CC to the generic address.
3. You can use customized subject for few user and the generic subject for the rest.
4. You can have customized message body for few user and the generic content for the rest.
5. You can use customized signatures for few user and the generic signature for the rest.
6. You can also have attachments for few users.

Here is the code for the same :

Sub automated_email()
    
    Dim counter As Long
    
    Dim outlookApp As Outlook.Application
    Dim outlookMail As MailItem
    Set outlookApp = New Outlook.Application
    
    Dim bodyString As String
    
    For counter = 5 To formatEmail.Cells(formatEmail.Rows.Count, "A").End(xlUp).Row

        Set outlookMail = outlookApp.CreateItem(outlookMailItem)
        outlookMail.To = formatEmail.Range("B" & counter).Value
        
        If formatEmail.Range("C" & counter).Value <> "" Then
            outlookMail.CC = formatEmail.Range("C" & counter).Value
        Else
            outlookMail.CC = formatEmail.Range("C2").Value
        End If
        
        outlookMail.BCC = formatEmail.Range("D" & counter).Value
        
        If formatEmail.Range("E" & counter).Value <> "" Then
            outlookMail.Subject = formatEmail.Range("E" & counter).Value
        Else
            outlookMail.Subject = formatEmail.Range("E2").Value
        End If
        
        bodyString = "Hi "
        
        If formatEmail.Range("A" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("A" & counter).Value & "," & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("A2").Value & "," & vbNewLine & vbNewLine
        End If
        If formatEmail.Range("G" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("G" & counter).Value & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("G2").Value & vbNewLine & vbNewLine
        End If
        If formatEmail.Range("H" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("H" & counter).Value & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("H2").Value & vbNewLine & vbNewLine
        End If
        
        outlookMail.Body = bodyString
        
        If formatEmail.Range("F" & counter).Value <> "" Then
            If Dir(formatEmail.Range("F" & counter).Value) <> "" Then
                outlookMail.Attachments.Add formatEmail.Range("F" & counter).Value
            End If
        End If
        
'        Use outlookMail.Display to review before sending
        outlookMail.Display
        
'        Use outlookMail.Send to directly send without reviewing
'        outlookMail.Send
        
    
        Set outlookMail = Nothing
    Next
       
    Set outlookApp = Nothing
    
End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.
SUBHAJIT.
.
To Download the example file:
Please click this Google Drive Link