(Excel 2010) Sending Email with Attachment via Outlook

[dudolbudol]

Hi Everyone,

Please need someone's assistance, I don't know anything yet about VBA coding, is there any VBA codes or file that fits to my situation?

Because, I need to send an email to specific users with their unique login credentials and a PDF manual. Also, i will resend the same email monthly which is hard because it's around 700 emails.

Thank you.

 

[mole999]

most of the instructions are here, just need to be crafted for your specific needs. . > Mail from Excel with Outlook (VBA)

If outlook requires confirmation on each email then it is likely you are running an unrecognised / out of date antivirus, and it is to prevent spam

 

[dudolbudol]

Hi Mole999,

I'm currently using the below code it's running perfectly can you help me how to edit the body?

here's the sample.

Sub Sendemail()

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem

For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    With olMail
    
        .To = Cells(i, 1).Value
        .CC = Cells(i, 2).Value
        .Subject = Cells(i, 3).Value
        .Body = Cells(i, 4).Value
        .Attachments.Add "C:\Users\ABC\Desktop\manual.pdf"
        .Display
        ''.Send
        
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
Next

End Sub

 

[dudolbudol]

I just want to insert string variable into text body because each user have different username and password details. how to add this one in my current code? StringUsername: (the details should be come from the excel by row) StringPassword: (the details should be come from the excel by row)

 

[mole999]

I'll have to look at what i use

 

[dudolbudol]

Hi Mole999,

i tried to learn some code and now it's working well, but i'm not sure if this is the best practice. Next, i need to extract multiple emails from the same excel sheet because it was mixed with numbers and some unwanted characters. i know i need a VBA code again here, is there any you can recommend? Thank you.

Sub Sendemail()

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim strbody As String
Dim strUser As String
Dim strPass As String

    For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    
    strbody = "Dear Valued Tenant," & vbNewLine & vbNewLine & _
              "Please be informed that from today onwards, all complaint/ requisition must be channeled through our website https://real.com" & vbNewLine & _
              "Your Account Details:" & vbNewLine & _
              "Username:" & Cells(i, 4).Value & vbNewLine & _
              "Password:" & Cells(i, 5).Value & vbNewLine & vbNewLine & _
              "This online feature is user-friendly and in case you face any difficulty, please contact me at:" & vbNewLine & vbNewLine & _
              "Telephone: 02-4561234 (ext. 200)" & vbNewLine & _
              "Mobile: 0501234699" & vbNewLine & vbNewLine & _
              "Attached is a brief instruction manual on how to use this online feature."
              
    With olMail
    
        .To = Cells(i, 1).Value
        .CC = Cells(i, 2).Value
        .Subject = Cells(i, 3).Value
        .Body = strbody
        .Attachments.Add "C:\Users\Abc\Desktop\manual.pdf"
        .Display
        ''.Send
        
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
Next

End Sub

 

[mole999]

yes strbody was the way

if doing as a loop i would look at the associated email and find @ in the dtring to make that the next one to send out, as a further check, it the right most free column, add a marker from code to show it was processed