Profile

Cover photo
Surya Bhattarai
377,897 views
AboutPostsPhotosVideos

Stream

Surya Bhattarai

General Discussion  - 
 
 
Hi all,

I would appreciate if anyone can help me finalise a VBA (for excel) this this task. I have the data in two columns in an Excel worksheet. I would like to be able to generate a separate email message for each member and send it to a designated recipient with only one member's detail in each email.

Data
Member's Name     Location
Mike                           US
Joanne                      Canada
David                          UK

I want to be able to able to generate an email with a table with the following columns heading and information:
>>>
"The following member has joined the organisation:
Member's Name     Location
Mike                           US

Please do not respond to this email"
<<<<
I was able to read the data from the worksheet and generate the first line of the email message but I could not write the headings and members details in the email message in a tabular format. The email functionality is working properly as expected. Many thanks for your help.

I have copied the code here. Ideally, I would like to designate the sender email as well if possible.

Sheets("Members").Activate
Range("A2").Select

Do Until IsEmpty(ActiveCell)

    Dim objOutlook As Object
    Dim objOutlookMsg As Object
    Dim objOutlookRecip As Object
    Dim objOutlookFrom As Object
    Dim strHTML

    MemberName = ActiveCell
    Location = ActiveCell.Offset(0, 1)
    
    ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("FirstName LastName")
              objOutlookRecip.Type = olTo

              ' Set the Subject, Body, and Importance of the message.
              .Subject = "New Membership  - " & MemberName & " - " & Location
             
              'Set body format to HTML
 
            .BodyFormat = olFormatHTML
 
            .HTMLBody = "<HTML><BODY>The following member has joined the organisation:</BODY></HTML>"

             .Importance = olImportanceHigh  'High importance
             
             '.From = "newmembers@example.com"

               ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next
      
             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Send
             End If
          End With
          Set objOutlook = Nothing
          
         Sheets("Members").Activate
         ActiveCell.Offset(1, 0).Select
              
Loop
      
End Sub
1
Surya Bhattarai's profile photoNicholas Perry's profile photo
4 comments
 
I have tried that. If I have two <HTML>s, the second one overwrites the first one. Splitting of HTML code, I think, is what I need.
Add a comment...

Surya Bhattarai

Discussion  - 
 
Hi all, 

I'm fairly new to html. I would appreciate if anyone could help me align the website pages to centre by default. This is the template I'm using http://www.quackit.com/html/templates/download/organization/12/html/. Source code is available for download at http://www.quackit.com/html/templates/organization_website_templates.cfm.

Thank you in advance.
1
Surya Bhattarai's profile photocostantino fusco's profile photo
4 comments
 
It worked!!! Many thanks for your help.
Add a comment...

Surya Bhattarai

Discussion  - 
 
Hi all, I'm considering a master of data science degree. Is there any basic online resources on data science that I can refer to before I enrol in the program?
1
Alex Lin's profile photoSurya Bhattarai's profile photo
2 comments
 
Thank you. I noticed that too but it doesn't have any session information as yet.
Add a comment...
 
Solution to the world's privacy concern on Google or similar glasses, permanent glass number issues by the Wearable Gadget Authority! Similar to the RTA for motor vehicle.
1
Add a comment...

Surya Bhattarai

General Discussion  - 
 
Hi all,

I would really appreciate if someone can help me create a formula to calculate each column for a particular month, generally for a previous month. I need to run this report for the previous month and use the value elsewhere. I can write the VBA but could not work out the sumif function.

Thank you.

DATE                   HEADING 1        HEADING 2          HEADING 3
28/05/2013        38,991,068         38,390,074            1,591 
29/05/2013        39,180,169         38,634,509            1,076 
30/05/2013        39,000,303         38,432,291            1,099 
31/05/2013        38,765,758         38,208,547            1,154 
1/06/2013          21,208,205         20,665,943             718 
1
Add a comment...

Surya Bhattarai

Math Questions  - 
 
Hi guys, just wondering if someone can help me solve this problem. I also need to see how the solution is derived.

Question: "A bomber takes off and flies at 500kph. A fighter, setting out 2 and 1/2 hours later flies at 750kph. How long will it take for the fighter to catch the bomber, and how far will the bomber have travelled at this time?".

Thank you.
1
Morten Nielsen's profile photoSurya Bhattarai's profile photoKenneth Weng's profile photoKimo Salen's profile photo
6 comments
 
2.5 x 500 = 1250
1250/(750-500)=5
5+2.5=7.5
7.5 x 500 = 3750
Add a comment...

Surya Bhattarai

General Discussion  - 
 
Hi all,

I would appreciate if anyone can help me finalise a VBA (for excel) this this task. I have the data in two columns in an Excel worksheet. I would like to be able to generate a separate email message for each member and send it to a designated recipient with only one member's detail in each email.

Data
Member's Name     Location
Mike                           US
Joanne                      Canada
David                          UK

I want to be able to able to generate an email with the following message:

"The following member has joined the organisation:
Member's Name     Location
Mike                           US
Please do not respond to this email"

I was able to read the data from the worksheet and generate the first line of the email message but I could not write the headings and members details in the email message in a tabular format. The email functionality is working properly as expected. Many thanks for your help.
1
Surya Bhattarai's profile photoDarton Williams's profile photo
2 comments
 
Thanks for your comments. I'm fairly new to the programming. Would be great if you could point out where I should fix it. I have copied the code here. Ideally, I would like to designate the sender email as well if possible.

Sheets("Members").Activate
Range("A2").Select

Do Until IsEmpty(ActiveCell)

    Dim objOutlook As Object
    Dim objOutlookMsg As Object
    Dim objOutlookRecip As Object
    Dim objOutlookFrom As Object
    Dim strHTML

    MemberName = ActiveCell
    Location = ActiveCell.Offset(0, 1)
    
    ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

          With objOutlookMsg
              ' Add the To recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add("FirstName LastName")
              objOutlookRecip.Type = olTo

              ' Set the Subject, Body, and Importance of the message.
              .Subject = "New Membership  - " & MemberName & " - " & Location
             
              'Set body format to HTML
 
            .BodyFormat = olFormatHTML
 
            .HTMLBody = "<HTML><BODY>The following member has joined the organisation:</BODY></HTML>"

             .Importance = olImportanceHigh  'High importance
             
             '.From = "newmembers@example.com"

               ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next
      
             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Send
             End If
          End With
          Set objOutlook = Nothing
          
         Sheets("Members").Activate
         ActiveCell.Offset(1, 0).Select
              
Loop
      
End Sub
Add a comment...

Surya Bhattarai

Shared publicly  - 
 
@Blackbutt Reserve, Central Coast, NSW
2
Add a comment...

Surya Bhattarai

YOUR Canon photos  - 
 
450D
13
1
Eric Salem's profile photo
Add a comment...
Links